TSQL:触发器中的 Try-Catch 事务
我正在尝试使用 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
相关文章