TSQL：触发器中的尝试捕获事务 [英] TSQL: Try-Catch Transaction in Trigger

问题描述

我正在尝试使用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

推荐答案

不要在触发器中回滚，也不需要启动事务。

Don't rollback in a trigger and there is no need to start a transaction either.

ROLLBACK TRANSACTION 将回滚原始DML触发器 和额外的触发器事务。因此该批次将被终止

The ROLLBACK TRANSACTION will rollback the original DML trigger and the extra trigger transaction too. So the batch will be aborted

编辑：

我建议您的catch块中不要包含 RETURN并简单地让代码完成
，我从来没有忽略触发器中的陷阱错误（但是我确实在触发器中使用TRY / CATCH并使用回滚和raiserror重新抛出），所以这是一个猜测，但是返回

I suggest not having a "RETURN" in your catch block and simply allow the code to complete I've never ignored a trapped error in a trigger (but I do use TRY/CATCH in triggers with rollback and raiserror to re-throw) so this is a guess, but the return is probably an abnormal exit condition in the trigger

另外，请尽量避免出现错误情况。更改-其他一些SQL 以避免错误。例如，添加 if exist（... 以测试是否有重复的第一个或相似的

Also, try to avoid the error condition in the first place. Change the --some more sql to avoid the error. Example, add if exists(... to test for a duplicate first or similar

这篇关于TSQL：触发器中的尝试捕获事务的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！