关于roll rollback命令 [英] In regard too rollback command

问题描述

我们创建了一个表，例如有10条记录。例101,102,103,104,105,106,107,108,109,110



第一步我们插入了5条记录并创建了一个保存点A

第二步我们更新了5条记录并创建了一条保存点B

第3步我们删除了5条记录并创建了一个保存点C.

删除的记录例如是101,103,105,107和109.现在我们输入命令回滚到保存点B ;将回滚所有已删除的记录。



**我的问题是可以回滚特定记录，如103和107而不是所有已删除的5条记录吗？**



我尝试了什么：



尝试但无法获得结果。

We have created a table for example with 10 records. Example 101,102,103,104,105,106, 107,108,109,110

1st step we had inserted 5 records and created a savepoint A
2nd step we have updated 5 records and created a savepoint B
3rd step we have deleted 5 records and created a savepoint C.
The deleted records were for example 101,103,105,107 and 109. Now if we type command rollback to savepoint B; All the deleted records will be rolled back.

**My question is it possible to rollback specific records such as 103 and 107 instead of all the 5 records which have been deleted?**

What I have tried:

tried but not able to get the result.

推荐答案

否： MSDN -

嵌套交易 [ ^ ]

No: MSDN -
Nesting Transactions[^]

Quote：

提交内部交易被SQL Server数据库引擎忽略。根据最外层事务结束时采取的操作，提交或回滚事务。如果提交了外部事务，则还会提交内部嵌套事务。如果外部事务被回滚，那么所有内部事务也会回滚，无论内部事务是否单独提交。

Committing inner transactions is ignored by the SQL Server Database Engine. The transaction is either committed or rolled back based on the action taken at the end of the outermost transaction. If the outer transaction is committed, the inner nested transactions are also committed. If the outer transaction is rolled back, then all inner transactions are also rolled back, regardless of whether or not the inner transactions were individually committed.

实现这一目标的唯一方法是拥有两个独立的事务：一旦提交了第一个事务，就可以启动第二个事务。回滚第二个不会回滚第一个。

The only way to achieve this would be to have two independent transactions: once the first one is committed, the second transaction can be started. Rolling back the second will not roll back the first.

您可以使用触发器将已删除或更改的记录移动到备份表有关删除。

然后，如果需要，您可以从该表恢复记录。

另一种选择是在表中添加已删除字段并使用该字段作为一个标志，但我认为这不是一个优雅的解决方案，使用第一个解决方案时性能也会更好。



SQL Server的一个例子，未经测试，但是应该工作：

You could move records that are deleted or changed to a "backup" table using a trigger on deletion.
Then if needed you can restore records from that table.
Another option would be to add a field "deleted" in your table and use that as a flag, but that is not an elegant solution I think, performance will also be better when using the first solution.

An example for SQL Server, not tested, but should work:

CREATE TRIGGER ondeleteTrigger
    ON database1.dbo.table1
    FOR DELETE
AS
    INSERT INTO database2.dbo.backupTable SELECT * FROM DELETED
GO

有关 CREATE TRIGGER 的更多信息，请访问： CREATE TRIGGER（Transact-SQL）| Microsoft Docs [ ^ ]

More information on CREATE TRIGGER can be found here: CREATE TRIGGER (Transact-SQL) | Microsoft Docs[^]

这篇关于关于roll rollback命令的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！