忽略触发器中的错误 [英] Ignoring errors in Trigger

问题描述

我有一个存储过程，该过程在Insert / Update / Delete的触发器中被调用。

I have a stored procedure which is called inside a trigger on Insert/Update/Delete.

问题是此SP内有某个代码块并不重要。
因此，我想忽略此代码块引起的任何错误。

The problem is that there is a certain code block inside this SP which is not critical. Hence I want to ignore any erros arising from this code block.

我将此代码块插入了TRY CATCH块中。但是令我惊讶的是，我得到了以下错误：

I inserted this code block inside a TRY CATCH block. But to my surprise I got the following error:

当前事务无法提交，并且不支持写入日志文件的操作。回滚交易。

The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

然后我尝试使用SAVE& ROLLBACK TRANSACTION和TRY CATCH一起也失败，并出现以下错误：

Then I tried using SAVE & ROLLBACK TRANSACTION along with TRY CATCH, that too failed with the following error:

当前事务无法提交，也无法回滚到保存点。回滚整个事务。

The current transaction cannot be committed and cannot be rolled back to a savepoint. Roll back the entire transaction.

我的服务器版本是：Microsoft SQL Server 2008（SP2）-10.0.4279.0（X64）

My server version is: Microsoft SQL Server 2008 (SP2) - 10.0.4279.0 (X64)

样本DDL：

IF OBJECT_ID('TestTrigger') IS NOT NULL
    DROP TRIGGER TestTrigger
GO
IF OBJECT_ID('TestProcedure') IS NOT NULL
    DROP PROCEDURE TestProcedure
GO
IF OBJECT_ID('TestTable') IS NOT NULL
    DROP TABLE TestTable
GO

CREATE TABLE TestTable (Data VARCHAR(20))
GO

CREATE PROC TestProcedure       
AS      
BEGIN 

    SAVE TRANSACTION Fallback
    BEGIN TRY
        DECLARE @a INT = 1/0
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION Fallback
    END CATCH
END
GO

CREATE TRIGGER TestTrigger
ON TestTable
FOR INSERT, UPDATE, DELETE 
AS
BEGIN
    EXEC TestProcedure   
END
GO

要复制的代码错误：

BEGIN TRANSACTION
INSERT INTO TestTable VALUES('data')
IF @@ERROR > 0
    ROLLBACK TRANSACTION
ELSE
    COMMIT TRANSACTION
GO

推荐答案

我建议重新进行架构设计，以免中毒原始交易-也许让交易发送服务经纪人消息（或只是将相关数据插入某种形式的队列表），以便非关键部分可以在完全独立的交易中发生。

I'd suggest re-architecting this so that you don't poison the original transaction - maybe have the transaction send a service broker message (or just insert relevant data into some form of queue table), so that the "non-critical" part can take place in a completely independent transaction.

例如您的触发器将变为：

E.g. your trigger becomes:

CREATE TRIGGER TestTrigger
ON TestTable
FOR INSERT, UPDATE, DELETE 
AS
BEGIN
    INSERT INTO QueueTable (Col1,Col2)
    SELECT COALESCE(i.Col1,d.Col1),COALESCE(i.Col2,d.Col2) from inserted i,deleted d
END
GO

您不应该在触发器中执行可能会失败的任何操作，除非您 do 想要强制启动触发器操作的事务也失败。

You shouldn't do anything inside a trigger that might fail, unless you do want to force the transaction that initiated the trigger action to also fail.

这篇关于忽略触发器中的错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！