如何忽略触发器中的错误并在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演示

SQLFiddle DEMO

这篇关于如何忽略触发器中的错误并在MS SQL Server中执行相应的操作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！