如何部分回滚SQL Server事务？ [英] How to rollback SQL server transaction partially ?

问题描述

我想部分回滚交易。实际上我有一个程序可以完成一些任务。我想记录每一步的状态。为此，希望在日志表中插入一行。如果有任何异常，我想知道完全执行了多少步骤以及发生了哪个步骤异常。



目前，如果发生任何异常，则回滚数据库中的所有更改。如果有任何解决方法 ，请告诉我。



我尝试过：



我仍​​在为此找到一个解决方案。

Hi,

I want to rollback transaction partially. Actually I have a procedure which does some tasks. I want to log the state of on each step. For this want to insert a row in the log table. In case of any exception I want to know how many steps are executed completely and on which step exception occurred.

Currently if any exception occurred then all the changes in database are rolled back. If there is any workaround for this then please let me know.

What I have tried:

I am still finding a soln for this.

推荐答案

你无法回滚部分事务 - 它是全部或全部。

即使您嵌套事务，内部级别COMMIT指令也会被忽略，内部级别ROLLBACK将导致错误：嵌套交易 [ ^ ]

插入一个不会回滚的日志条目，无论事务是可能的（但令人讨厌） - 使用xp_cmdshell并在您的日志中使用INSERT语句执行osql。它会起作用 - 因为它是一个单独的会话 - 但它会变得很慢！

You can't rollback "part" of a transaction - it's all or nothing.
Even if you nest transactions, inner level COMMIT instructions are ignored, and inner level ROLLBACK will cause an error: Nesting Transactions[^]
To insert a log entry which won't be rolled back regardless of the transaction is possible (but nasty) - use xp_cmdshell and execute osql with an INSERT statement into your log. It'll work - because it's a separate session - but it'll be dog slow!

这篇关于如何部分回滚SQL Server事务？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！