TSQL:触发器中的 Try-Catch 事务

2022-01-01 00:00:00 tsql sql-server triggers

我正在尝试使用 Microsoft Server 2005 将 try-catch 语句放入触发器中.

I am trying to put a try-catch statement inside a trigger using Microsoft Server 2005.

BEGIN TRANSACTION
BEGIN TRY
    --Some More SQL
    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    IF (XACT_STATE()) = -1
    BEGIN
        ROLLBACK TRANSACTION;
    END;
END CATCH

问题是，如果 try-catch 块捕获到某些内容，我不希望触发器失败.目前，我收到错误事务在触发器中结束.批处理已中止."如果交易失败.如何让触发器优雅地失败?

The problem is that I don't want the trigger to fail if something is caught by the try-catch block. At the moment, I am getting the error "The transaction ended in the trigger. The batch has been aborted." if the transaction fails. How can I get the trigger to fail gracefully?

此外，如果我删除事务，我会收到错误事务在触发器中注定失败.批处理已中止.".

Additionally, if I remove the transaction, I get the error "Transaction doomed in trigger. Batch has been aborted.".

BEGIN TRY
    --Some More SQL
END TRY
BEGIN CATCH
    return
END CATCH

有什么办法可以解决这个问题吗?

Is there any way around this?

推荐答案

根据我的经验，在触发器的 try catch 中捕获的任何错误都会回滚整个事务；您也许可以使用保存事务.我认为您需要查看更多 sql"中发生的情况，并确定您是否可以围绕它编写 case/if 语句来阻止错误.

In my experience any error caught in a try catch in a trigger will rollback the entire transaction; you may be able to use a save transaction. I think you need to look at whats happening in "Some more sql" and determine if you can write case / if statements around it to stop the error.

根据您正在做的事情，您可以做的是使用 save交易并在catch中捕获它

What you may be able todo depending on what you are doing is use a save transaction and capture that in the catch

在你的代码中是这样的

SAVE TRANSACTION BeforeUpdate;
BEGIN TRY
        --Some More SQL
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION BeforeUpdate;
        return
END CATCH

相关文章