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)