TSQL make trigger fail silently

Question

I have some code in an after insert trigger that may potentially fail. Such a failure isn't crucial and should not rollback the transaction. How can I trap the error inside the trigger and have the rest of the transaction execute normally?

The example below shows what I mean. The trigger intentionally creates an error condition with the result that the original insert ( "1" ) never inserts into the table. Try/Catch didn't seem to do the trick. A similar, older stack overflow question didn't yield an answer except for "prevent the error from occuring in the first place" - which isn't always possible/easy.

Any other ideas?

create table test 
(
  a int not null
);
go

create trigger testTrigger on test 
after insert as 
begin 
  insert into test select null;
end;
go

insert into test values ( 1 );

Answer 1

A trigger cannot fail and still have the transaction roll forward. You have a few options to ensure that the trigger does not fail.

1 - You can ensure that the after does not fail by duplicating the logic for checking the constraints and not attempting an operation which would violate the constraints:

i.e.

INSERT INTO test WHERE val IS NOT NULL

2 - You can defer the potentially failing action by using a queue design pattern where actions which may or may not fail are queued by enqueueing to a table where the enqueueing operation cannot possibly fail.

i.e.

INSERT INTO ACTION_QUEUE (action, parameters) VALUES ('INSERT INTO TEST', val)

Answer 2

Due to the way triggers are implemented in SQL Server, all constraint violations within the triggers doom the transactions.

This is the same as doing:

DROP TABLE test

CREATE TABLE test 
(
        a INT NOT NULL
)

GO

SET XACT_ABORT ON
GO

BEGIN TRANSACTION

BEGIN TRY
        INSERT
        INTO    test
        SELECT  NULL
END TRY
BEGIN CATCH
        INSERT
        INTO    test
        SELECT  1
END CATCH

which results in a doomed transaction, except that there is no way to disable XACT_ABORT inside a trigger.

SQL Server also lacks autonomous transactions.

That's another reason why you should put all you logic into the stored procedures rather than triggers.