TSQL triggers an error trigger

Question

I have code in a trigger after insertion that could potentially not work. Such a failure is not critical and should not roll back the transaction. How can I catch an error inside a trigger and execute the rest of the transaction in normal mode?

The example below shows what I mean. The trigger intentionally creates an error condition, as a result of which the original insert ("1") is never inserted into the table. Try / catch does not seem to be a trick. A similar question from older did not give an answer, except how to "prevent the occurrence of errors in the first place" - which is not always possible / simple.

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

The trigger cannot fail and is still executing the transaction. You have several options to ensure that the trigger does not work.

1 - You can guarantee that after this there will be no duplication of logic for checking restrictions and will not try to perform an operation that violates the restrictions:

i.e.

 INSERT INTO test WHERE val IS NOT NULL 

2 - You can delay a potentially unsuccessful action using the queue design pattern, where actions that may or may not fail are queued on a table in which the queue operation may not complete.

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 violations of restrictions in triggers doom 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 leads to a doomed transaction, except that inside the trigger there is no way to disable XACT_ABORT .

SQL Server also lacks offline transactions.

This is another reason why you should put all the logic in stored procedures, not triggers.

Answer 3

• You can disable XACT_ABORT inside the trigger (use caution)
• You can invoke a trigger call to a stored procedure. (I am now struggling with the opposite problem: I want the transaction to be aborted, but since the logic is in the SP called by the trigger, not the trigger itself, this does not happen.)