TSQL使触发器无效地失败 [英] TSQL make trigger fail silently

问题描述

我有一个后插入触发器可能会失败的代码。这样的失败并不重要，应该不会回滚事务。如何捕获触发器中的错误，并使其余的事务正常执行？

下面的例子显示了我的意思。触发器有意创建错误条件，结果原始插入（1）不会插入到表中。尝试/抓住似乎没有办法。类似的较旧的堆栈溢出问题没有产生答案除了防止错误发生在第一位 - 这并不总是可能/容易。

任何其他想法？

 创建表格测试
 （
a int not null 
）; 
 go 
 
创建触发器testTrigger测试
插入为
 begin 
 insert into test select null; 
结束
 go 
 
插入测试值（1）; 
  

解决方案

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

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)

这篇关于TSQL使触发器无效地失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！