保持在数据库中的数据更改的历史记录 [英] Keeping a history of data changes in database
问题描述
数据的一些行数据库中的每个变化应保存在某种历史上一行数据，以便用户可以回滚到以前的行数据的状态。是否有这种做法的好的做法呢？ 。试着用DataContract和序列化和反序列化数据对象，但它变得复杂对象有些凌乱
Every change of data in some row in database should save the previous row data in some kind of history so user can rollback to previous row data state. Is there any good practice for that approach? Tried with DataContract and serializing and deserializing data objects but it becomes little messy with complex objects.
因此，为了更清楚：
So to be more clear:
-
我使用NHibernate的数据访问，并希望留出了数据库的依赖（对于使用SQL Server 2005的测试）
I am using NHibernate for data access and want to stay out off database dependency (For testing using SQL server 2005)
什么是我的本意是提供数据的历史记录，以便每次用户可以回滚到以前的一些版本。
What is my intention is to provide data history so every time user can rollback to some previous versions.
用法的一个例子是以下内容：
An example of usage would be the following:
- 我有一个新闻文章
- 有人做出一些改变该文章
- 主编看到这则新闻有一些错别字
- 它决定回滚到以前的有效版本（至最新版本被纠正）
- I have a news article
- Somebody make some changes to that article
- Main editor see that this news has some typos
- It decides to rollback to previous valid version (until the newest version is corrected)
我希望我给你有效的信息。
I hope I gave you valid info.
推荐答案
存储在主表的变化被称为审计表变化表。你可以这样做多种方式：
Tables that store changes when the main table changes are called audit tables. You can do this multiple ways:
- 在使用触发器数据库：我会推荐这种方法，因为再有没有办法，数据可以改变，而不做了笔录。你必须这样做时考虑到3种类型的更改：添加，删除，更新。因此，您需要触发功能，将在所有三个工作。
- In the database using triggers: I would recommend this approach because then there is no way that data can change without a record being made. You have to account for 3 types of changes when you do this: Add, Delete, Update. Therefore you need trigger functionality that will work on all three.
还记得一个事务可以在同一时间修改多条记录，所以你应与整套修改的记录，而不仅仅是最后一个记录（因为大多数人姗姗来迟意识到他们所做的）工作。
Also remember that a transaction can modify multiple records at the same time, so you should work with the full set of modified records, not just the last record (as most people belatedly realize they did).
控制不会返回到调用程序直到触发执行完成。所以你应该保持代码的轻和尽可能快
Control will not be returned to the calling program until the trigger execution is completed. So you should keep the code as light and as fast as possible.
- 在中间层使用代码：这种方法可以让你更改保存到不同的数据库，并可能采取一些负载关闭数据库。但是，运行UPDATE语句在SQL程序员将完全绕过中间层，你会不会有一个审计线索。
- In the middle layer using code: This approach will let you save changes to a different database and possibly take some load off the database. However, a SQL programmer running an UPDATE statement will completely bypass your middle layer and you will not have an audit trail.
审核表的结构
您将有以下栏目：结果
自动编号PK，时间戳，操作类型+所有从原始表中的列结果
我已经在过去通过以下方式做到了这一点：
You will have the following columns:
Autonumber PK, TimeStamp, ActionType + All columns from your original table
and I have done this in the following ways in the past:
< STRONG>表结构：结果
自动编号PK，时间戳，操作类型，表名，OriginalTableStructureColumns
这结构将意味着你每创建保存的数据表中的一个审计表。数据保存和重建是很容易做到的。我会推荐这种方法。
命名值对：结果
自动编号PK，时间戳，操作类型，表名，PKColumns，的ColumnName，的OldValue，的NewValue
This structure will mean that you create one audit table per data table saved. The data save and reconstruction is fairly easy to do. I would recommend this approach.
Name Value Pair:
Autonumber PK, TimeStamp, ActionType, TableName, PKColumns, ColumnName, OldValue, NewValue
这结构将让您保存任何表，但是你必须在你的触发每一列创建名称值对。这是非常通用的，但价格昂贵。您还需要编写一些看法通过unpivoting数据重建的实际行。这得到是乏味的，而不是通常采用的方法。
This structure will let you save any table, but you will have to create name value pairs for each column in your trigger. This is very generic, but expensive. You will also need to write some views to recreate the actual rows by unpivoting the data. This gets to be tedious and is not generally the method followed.
这篇关于保持在数据库中的数据更改的历史记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
