在现有CRUD应用程序中实现数据回滚
我有一个现有的 CRUD 应用程序，我的任务是实现“坟墓乱石”。我需要一种方法来允许用户将给定的数据页回滚到之前的状态。
假设我在此应用程序的页面上有名字、姓氏和社会安全号码。用户 A 更新姓氏字段。随后，用户 B 注意到新姓氏不同，并希望查看是谁更改了它，并在必要时将其回滚。
我对此不熟悉，因此如果我遗漏或误用了某些术语，请原谅我。
这个应用程序有一个 MS SQL 后端，DAL 主要是 SPROCS。现在没有任何档案或墓碑可言。
我曾想过为每个名为 tblPerson 的现有表创建一个表 --> tblPersonTombstone，然后回滚部分将从该表中读取。不幸的是，最初的数据库设计者的设计方式是应用程序中的单个页面可能包含来自 2 或 3 个不同表的信息。因此，我想，我需要一种更加基于事务的方法。
任何方向或指示将不胜感激。我的想法是否走在正确的轨道上？也许我把它复杂化了？其他人是如何做到的？
我看到这篇文章如何在.net中实现“撤消”操作Windows 应用程序？ 还有这个新的 CodePlex 项目：一个简单的撤消/重做框架，但我担心这都不适合我的实际情况。我不想让用户单击 ctl+z。我需要让他们将整个页面回滚到之前的状态。如果我误解了这两个例子的用法，请说出来。
谢谢你的时间。
I have an existing CRUD app that I have been tasked with implementing "tomb stoning". I need a way to allow a user to roll a given page of data back to the previous state.
Say I have First Name, Last Name, and Social Security Number on a page in this app. User A updates the Last Name field. Later, User B notices that the New Last Name is different and wants to see Who changed it and roll it back if neccessary.
I am unfamilar with this so if I am missing or misusing some terms, forgive me.
This app has a MS SQL backend and the DAL is mostly SPROCS. Right now there isn't any archiving or tomb stoning to speak of.
I had thought to just make a table for each existing table called tblPerson --> tblPersonTombstone and then the roll back portion would read from that table. Unfortunately, the original DB designers designed it in such a way that a Single page in the App might contain info from 2 or 3 different tables. Thus, I would imagine, I need a more Transaction based approach.
Any direction or pointers will be greatly appreciated. Am I on the right track with my thinking? Maybe I am over complicating it? How have others done it?
I see this post How to implement ‘undo’ operation in .net windows application? and also this one New CodePlex project: a simple Undo/Redo framework but I am concered that neither fit my actual situaiton. I am not looking to let the users click ctl+z. I need to let them roll a whole page back to a previous state. If I am misunderstanding the use of those two examples then please say so.
Thanks for the time.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
你所说的属于审计主题。不幸的是，这是涉及较多的实现之一。
这是一个最佳实践：
创建新的“修订表”来镜像正在审核的表，但还包括一些额外的元数据（修订号、时间戳、进行更改的用户、CRUD 操作的类型）。
这个想法是能够轻松地在任何时间点获得记录的完整快照，然后使用它来进行完美的回滚。您完全相信数据是正确的并且它确实有效。
大多数人使用触发器来填充这些修订记录。
还有其他解决方案。显然，这样做将非常耗时且占用磁盘空间（但是，嘿，您可以安全地清除旧记录，而不会破坏系统）。优点是您最终会获得很大的灵活性。
这就是大多数人这样做的方式。
这是另一种方法：
我还实现了一种更简单的审核模式，该模式仅跟踪已更改的表的名称、已更改的字段、旧值和新值以及常用的元数据。
这样，我为我的 ORM 工具编写了一个插件，它几乎可以自动处理审核数据的保存。否则这会非常乏味。
您可能会想走这条路。确实，您可能可以从中得到回滚。但这会更加困难。例如，如果您想要恢复到任何给定的日期和时间，则必须分析所有这些单独的字段级更改记录，以便重新创建完整的快照。如果你改变了一个字段的名称，你就有祸了！
因此，这种审计方法非常适合生成和显示审计跟踪，但如果您想进行回滚，它有更多的移动部件，并且有更多可能出错的事情。请注意我的话：如果您需要回滚，请远离此，这将比创建这些修订表更多工作！
链接
这是 stackoverflow 上的一个链接，他们在其中讨论在 SQL Server 中实现审计时，有些人提到了 SQL 2008 Enterprise 中新增的更改数据捕获...它不会自动回滚，但会存储审计数据：
在 SQL Server 中实现审计表的建议？
What you're talking about falls under the topic of auditing. Unfortunately, this is one of the more involved implementations.
Here's a Best Practice:
Create new "revision tables" that mirror the tables being audited, but also include some extra metadata (revision #, timestamp, user who made the change, type of CRUD operation).
The idea is to be able to easily get a complete snapshot of the record at any point in time, and then use that to do a perfect rollback. You have complete confidence that the data is right and it just works.
Most folks use triggers to populate those revision records.
There are other solutions as well. Obviously doing it this way is going to be time consuming and disk space-intensive (but hey, you can safely purge old records without breaking the system). The advantage is that you end up with a ton of flexibility.
So that's the way most people do it.
Here's another way:
I have also implemented a simpler auditing pattern that just keeps track of the name of the table that changed, the field that changed, the old value and the new value, along with the usual metadata.
With that, I wrote a plugin for my ORM tool that pretty much handles the saving of auditing data automatically. Otherwise this would have been very tedious.
You might be tempted to go this route. And it's true, you probably can get rollbacks out of it. But it would be more difficult. For instance, if you wanted to restore to any given date and time, you would have to analyze all those individual field-level change records in order to re-create the complete snapshot. And woe unto you if you ever change the name of a field!
So this method of auditing is fine for generating and displaying an audit trail, but if you want to do rollbacks, it has a lot more moving parts, and a lot more things that can go wrong. Heed my words: stay away from this if you need rollbacks, it's going to be more work than just creating those revision tables!
Links
Here's a link on stackoverflow where they talk about implementing auditing in SQL Server, some people mention Change Data Capture which is new in SQL 2008 Enterprise... It doesn't do rollbacks automatically, but it does store audit data:
Suggestions for implementing audit tables in SQL Server?
2 个想法：
创建一个归档表，该表不一定是持久表的副本，而只是表示具有回滚功能的页面上的数据。因此，如果页面包含影响多个表的字段，则您的存档表将包含页面上每个可更改字段的列。
如果页面上的数据封装在单个 DTO 或实体对象中，请在更改对象之前序列化该对象并将其存储在存档表中。然后如果用户希望回滚，可以反序列化，然后保存反序列化的对象。
2 thoughts:
Create an archive table that is not necessarily a duplicate of your persisting table(s) but justrepresents the data on the page with the rollback capability. So if the page contains fields that impact multiple tables, your archive table wil contain columns for each alterable field on the page.
If the data on the page is encapsulated in a single DTO or entity object, serialize the object before chnging it and store it in anarchive table. Then if the user wishes to roll back, you can deserialize it and then save the deserialized object.