如何在 SQL Server 2005 中回滚 UPDATE 查询？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/721471/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I rollback an UPDATE query in SQL server 2005?
提问by Guddu
How can I rollback an UPDATE query in SQL server 2005?
如何在 SQL Server 2005 中回滚 UPDATE 查询？
I need to do this in SQL, not through code.
我需要在 SQL 中执行此操作，而不是通过代码。
回答by Adam Robinson
begin transaction
// execute SQL code here
rollback transaction
If you've already executed the query and want to roll it back, unfortunately your only real option is to restore a database backup. If you're using Full backups, then you should be able to restore the database to a specific point in time.
如果您已经执行了查询并想要回滚它，不幸的是，您唯一真正的选择是恢复数据库备份。如果您使用完整备份，那么您应该能够将数据库恢复到特定时间点。
回答by rick schott
You need this tool and you can find the transaction and reverse it.
你需要这个工具，你可以找到交易并撤销它。
回答by tarzanbappa
You can use implicit transactionsfor this
您可以为此使用隐式事务
SET IMPLICIT_TRANSACTIONS ON
update Staff set staff_Name='jas' where staff_id=7
ROLLBACK
As you request-- You can SET this setting ( SET IMPLICIT_TRANSACTIONS ON) from a stored procedure by setting that stored procedure as the start up procedure.
根据您的要求——您可以SET IMPLICIT_TRANSACTIONS ON通过将存储过程设置为启动过程来从存储过程设置此设置 ( )。
But SET IMPLICIT TRANSACTION ONcommand is connection specific. So any connection other than the one which running the start up stored procedure will not benefit from the setting you set.
但SET IMPLICIT TRANSACTION ON命令是特定于连接的。因此，除了运行启动存储过程的连接之外的任何连接都不会从您设置的设置中受益。
回答by Frederik Gheysels
You can rollback the statements you've executed within a transaction. Instead of commiting the transaction, rollback the transaction.
您可以回滚您在事务中执行的语句。不是提交事务，而是回滚事务。
If you have updated something and want to rollback those updates, and you haven't done this inside a (not-yet-commited) transaction, then I think it's though luck ...
如果您更新了某些内容并想要回滚这些更新，并且您还没有在（尚未提交的）事务中完成此操作，那么我认为这只是运气......
(Manually repair, or, restore backups)
（手动修复或恢复备份）
回答by JoshBerke
Once an update is committed you can't rollback just the single update. Your best bet is to roll back to a previous backup of the database.
提交更新后，您不能仅回滚单个更新。最好的办法是回滚到数据库的先前备份。
回答by TheTXI
From the information you have specified, your best chance of recovery is through a database backup. I don't think you're going to be able to rollback any of those changes you pushed through since you were apparently not using transactions at the time.
根据您指定的信息，恢复的最佳机会是通过数据库备份。我认为您无法回滚您推动的任何更改，因为您当时显然没有使用事务。
回答by Rick B
Simple to do:
做起来很简单：
header code...
标题代码...
Set objMyConn = New ADODB.Connection
Set objMyCmd = New ADODB.Command Set
objMyRecordset = New ADODB.Recordset
On Error GoTo ERRORHAND
Working code...
工作代码...
objMyConn.ConnectionString = ConnStr
objMyConn.Open
code....
代码....
'Copy Data FROM Excel'
'从 Excel 复制数据'
objMyConn.BeginTrans <-- define transactions to possible be rolled back
For NewRows = 2 To Rows
objMyRecordset.AddNew
For NewColumns = 0 To Columns - 1
objMyRecordset.Fields(NewColumns).Value = ActiveSheet.Cells(NewRows, NewColumns + 1)
Next NewColumns objMyRecordset.Update Next NewRows
objMyConn.CommitTrans <- if success, commit them to DB
objMyConn.Close
ERRORHAND:
错误提示：
Success = False
objMyConn.RollbackTrans <-- here we roll back if error encountered somewhere
LogMessage = "ERROR writing database: " & Err.Description
...
...
回答by ???? ?????? Hadi Keshavarz
in this example we run 2 line insert into query and if all of them true it run but if not no run anything and ROLLBACK
在这个例子中，我们在查询中运行 2 行插入，如果所有这些都为真，则运行，但如果不是，则不运行任何内容并 ROLLBACK
DECLARE @rowcount int set @rowcount = 0 ;
BEGIN TRANSACTION [Tran1]
BEGIN TRY
insert into [database].[dbo].[tbl1] (fld1) values('1') ;
set @rowcount = (@rowcount + @@ROWCOUNT);
insert into [database].[dbo].[tbl2] (fld1) values('2') ;
set @rowcount = (@rowcount + @@ROWCOUNT);
IF @rowcount = 2
COMMIT TRANSACTION[Tran1]
ELSE
ROLLBACK TRANSACTION[Tran1]
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION[Tran1]
END CATCH
回答by mezoid
As already stated there is nothing you can do except restore from a backup. At least now you will have learned to always wrap statements in a transaction to see what happens before you decide to commit. Also, if you don't have a backup of your database this will also teach you to make regular backups of your database.
如前所述，除了从备份还原之外，您无能为力。至少现在你已经学会了总是在事务中包装语句，以便在你决定提交之前查看会发生什么。此外，如果您没有数据库的备份，这也将教您定期备份数据库。
While we haven't been much help for your imediate problem...hopefully these answers will ensure you don't run into this problem again in the future.
虽然我们对您的直接问题没有太大帮助...希望这些答案将确保您将来不会再次遇到此问题。
回答by Vinod
Try
尝试
ROLLBACK WORK;
It usually works
它通常有效
