触发器不能失败， 。您有几个选项来确保触发器不会失败。

1 - 您可以通过复制用于检查约束的逻辑来确保后端不会失败尝试违反约束的操作：

ie

  INSERT INTO test WHERE val IS NOT NULL 
 

2 - 您可以通过使用队列设计模式来延迟潜在的失败操作，其中可能或可能不会失败的操作通过入队到入队列操作不可能失败的表进行排队。 / p>

ie

  INSERT INTO ACTION_QUEUE（action，parameters）VALUES（'INSERT INTO TEST'，val）
 

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 );

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)