如何忽略触发器中的错误并在MS SQL Server中执行相应的操作 [英] How to Ignoring errors in Trigger and Perform respective operation in MS SQL Server
问题描述
我已经创建了AFTER INSERT TRIGGER
I have created AFTER INSERT TRIGGER
现在,如果在执行触发器时发生错误的话。
Now if any case if an error occurs while executing Trigger. It should not effect Insert Operation on Triggered table.
在一个单词中,如果触发器中发生任何错误,都应该忽略它。
In One word if any ERROR occurs in trigger it should Ignore it.
我使用过
BEGIN TRY
END TRY
BEGIN CATCH
END CATCH
但是它会给出以下错误消息并在已触发的表上回滚插入操作
But it give following error message and Rolled back Insert operation on Triggered table
在触发器执行期间引发了错误。
推荐答案
有趣的问题。默认情况下,触发器被设计为如果触发器失败,则会回滚触发它的命令。因此,无论何时执行触发器,都会有一个活动事务,无论是否存在显式的BEGIN TRANSACTION。并且触发器内的BEGIN / TRY也将不起作用。最佳实践是不要在触发器中编写任何可能会失败的代码-除非希望同时使触发语句失败。
Interesting problem. By default, triggers are designed that if they fail, they rollback the command that fired it. So whenever trigger is executing there is an active transaction, whatever there was an explicit BEGIN TRANSACTION or not on the outside. And also BEGIN/TRY inside trigger will not work. Your best practice would be not to write any code in trigger that could possibly fail - unless it is desired to also fail the firing statement.
在这种情况下,要抑制这种情况行为,有一些解决方法。
In this situation, to suppress this behavior, there are some workarounds.
选项A(丑陋的方式):
由于事务在触发开始时处于活动状态,因此您可以 COMMIT
并继续执行触发命令:
Since transaction is active at the beginning of trigger, you can just COMMIT
it and continue with your trigger commands:
CREATE TRIGGER tgTest1 ON Test1 AFTER INSERT
AS
BEGIN
COMMIT;
... do whatever trigger does
END;
请注意,如果触发代码中存在错误,则仍会产生错误消息,但其中的数据 Test1
表已安全插入。
Note that if there is an error in trigger code this will still produce the error message, but data in Test1
table are safely inserted.
选项B(也很丑):
您可以将代码从触发器移动到存储过程。然后从实现 BEGIN / TRY
的Wrapper SP调用该存储过程,最后,从触发器调用Wrapper SP。如果逻辑上需要(现在在SP中),从 INSERTED
表中移动数据可能有点棘手-可能使用一些临时表。
You can move your code from trigger to stored procedure. Then call that stored procedure from Wrapper SP that implements BEGIN/TRY
and at the end - call Wrapper SP from trigger. This might be a bit tricky to move data from INSERTED
table around if needed in the logic (which is in SP now) - probably using some temp tables.
SQLFiddle DEMO
这篇关于如何忽略触发器中的错误并在MS SQL Server中执行相应的操作的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!