TSQL make trigger fail silently

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 
begi开发者_开发知识库n 
  insert into test select null;
end;
go

insert into test values ( 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)

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.

1. You can turn XACT_ABORT off inside the trigger (use caution)
2. You can have the trigger call a stored procedure. (I am now wrestling with the opposite problem: I want the transaction aborted, but because the logic is in an SP called from the trigger, and not the trigger itself, this isn't happening.)