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屋!