Re: Transaction Abort in a trigger SQL 2008, writing to event log

Um, I think I see part of my issue just from your example. If I run my tests
using a stored proc, the error is logged fine, but if I use enterprise
manager, and hand enter data, the transaction is aborted in the trigger and
no error is logged.

Thanks for the example. I hope you hadthat code lying around and didn't
create just for me. :)

"Erland Sommarskog" wrote:

John Couch (JohnCouch@xxxxxxxxxxxxxxxxxxxxxxxxx) writes:
Well, I have tried about everything i can think of, including the CLR
and a loopback call to write to a log table. Nothing works. Even
shutting off xact_abort. I feel like I should resign myself to not
catching errors in SQL. I did go to your links and rated the posts to
very important (i think that was the highest), and hopefully someday
they will fix the issue. I appreciate all your feedback Erland. Not sure
where to go from here, but i'm sure ill figure out something.

If "nothing works", you are probably doing something you shouldn't. Here
is a simple working example. No loopbacks, no XACT_ABORT ON.

CREATE TABLE logtable (id int IDENTITY,
errno int NOT NULL,
errmsg nvarchar(2048),
errproc sysname,
errtime datetime,
CONSTRAINT pk_logtable PRIMARY KEY (id))
go
CREATE PROCEDURE error_handler_sp @procid int AS

DECLARE @errmsg nvarchar(2048),
@severity tinyint,
@state tinyint,
@errno int,
@proc sysname,
@lineno int

SELECT @errmsg = error_message(), @severity = error_severity(), -- 10
@state = error_state(), @errno = error_number(),
@proc = error_procedure(), @lineno = error_line()

IF @errmsg NOT LIKE '***%' -- 11
BEGIN
SELECT @errmsg = '*** ' + coalesce(quotename(@proc), '<dynamic SQL>') +
', ' + ltrim(str(@lineno)) + '. Errno ' +
ltrim(str(@errno)) + ': ' + @errmsg
END

INSERT logtable(errno, errmsg, errproc, errtime)
VALUES(@errno, @errmsg, object_name(@procid), getdate())
RAISERROR(@errmsg, @severity, @state)
go
CREATE TABLE testtable (somekey int NOT NULL,
somedata varchar(24) NOT NULL,
CONSTRAINT pk_testtable PRIMARY KEY(somekey)
)
go
CREATE TRIGGER testtable_tri ON testtable INSTEAD OF INSERT AS

BEGIN TRY
IF EXISTS (SELECT * FROM inserted WHERE somekey % 5 = 0)
RAISERROR('The key must not be divisible by 5', 16, 1)
IF EXISTS (SELECT * FROM inserted WHERE convert(int, somedata) > 100)
RAISERROR('somedata must not be > 100', 16, 1)
INSERT testtable(somekey, somedata)
SELECT somekey, somedata FROM inserted
END TRY
BEGIN CATCH
IF @@trancount > 0 ROLLBACK TRANSACTION
EXEC error_handler_sp @@procid
END CATCH
go
CREATE PROCEDURE insert_data @key int, @data varchar(29) AS
BEGIN TRY
INSERT testtable(somekey, somedata)
VALUES (@key, @data)
END TRY
BEGIN CATCH
IF @@trancount > 0 ROLLBACK TRANSACTION
EXEC error_handler_sp @@procid
END CATCH
go
EXEC insert_data 9, '12'
EXEC insert_data 20, 'lullull'
EXEC insert_data 21, 'ubbabubba'
go
SELECT * FROM logtable ORDER BY id
go
DROP PROCEDURE insert_data, error_handler_sp
DROP TABLE logtable, testtable







--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx


.

Relevant Pages

  • Re: Transaction Abort in a trigger SQL 2008, writing to event log
    ... catching errors in SQL. ... I appreciate all your feedback Erland. ... CREATE TABLE logtable (id int IDENTITY, ... EXEC error_handler_sp @@procid ...
    (microsoft.public.sqlserver.programming)
  • Re: Transaction Abort in a trigger SQL 2008, writing to event log
    ... catching errors in SQL. ... CREATE TABLE logtable (id int IDENTITY, ... DECLARE @errmsg nvarchar, ... EXEC error_handler_sp @@procid ...
    (microsoft.public.sqlserver.programming)
  • Re: Views vs Stored Procedures, whats the difference?
    ... I'm just trying to differentiate between two fundamentally different SQL objects. ... CREATE TABLE P(pk INT NOT NULL PRIMARY KEY, ... SELECT MAXFROM nestedview ...
    (comp.databases.ms-sqlserver)
  • Re: Full text catalog just not populating
    ... exec sp_defaultdb N'NT Authority\System', N'master' ... means either the network guys in my company who don't know SQL but are admins ... > needs this login to log into SQL Server and you can either add back this ... >> fetching U ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Indexing delay for one row.
    ... script and WAITFOR and varying the delay from 1 to 18 seconds. ... could you confirm your exact version of SQL Server that you are seeing ... It is possible that a change was made to the pooling frequence under SP3 to ... exec sp_fulltext_table 'FTSTable','create','FTSCatalog','FTSTable_IDX' ...
    (microsoft.public.sqlserver.fulltext)
Loading