当您需要“更改”行时，您添加一个新行，同时将上一个行中的 to 更新为到 Now 的行以及您要添加到 Now 的行中的来自。
您可以通过其中包含 where to = null 的视图从表中读取数据。
此方法还可以让您了解数据库在任何时间点的状态。
编辑
只是为了回应评论而澄清：序列将由表的主键给出，这将是一个自动增量数字。
Use "Insert Only Databases"
The basic idea is that you never update or delete data.
Each table has 2 datetime columns from and to.
They start with the value null in each (beginning of time to end of time)
When you need to "change" the row you add a new row, at the same time you update the to in the previous row to Now and the from in the row you are adding to Now.
You read data out of the table via a view that has a where to = null in it.
This method also gives you a picture of the state of your database at any point in time.
EDIT
Just to clarify in response to the comment: The sequence would be given by the primary key of the table, which would be an autoincrement number.
此技术的优点是为您提供表中每条记录的已知历史数据范围（如果每条记录是 WHERE TimeOfInterest > UpdatedTime ORDER BY UpdatedTime DESC 的记录中的 TOP 1，则该记录在给定时间内有效），并且具有较低的值开销（只是表上的一列）。 它也非常适合从不使用此方法的表进行转换，使用简单的 ALTER TABLE 来添加单个列（您可以一致地命名）。 然后，您只需更改 UNIQUE 约束以使用当前约束和 UpdatedTime 列的组合，并且需要更改一些查询。
Use an "insert only" database, as described by Shiraz Bhaji, but you can use a simpler technique. For each table that you need to maintain audit data for, just have an additional column for Updated Time, defaulting to now. When you make a change to a record, instead of updating, just do an insert with all your data; the UpdatedTime column will get the current time.
Note that this method means you have to break or reconsider your UNIQUE constraints; you can keep a primary key, but the uniqueness becomes a composite of your primary key and your UpdatedTime.
This technique has the advantage of giving you a known range of historical data for each record on the table (each record is valid for a given time if it is the TOP 1 of records WHERE TimeOfInterest > UpdatedTime ORDER BY UpdatedTime DESC) with a low overhead (just a single column on the table). It's also quite amenable to conversion from tables not using this method, with a simple ALTER TABLE to add a single column (which you can name consistently). Then you just need to alter your UNIQUE constraints to use a composite of their current contraints and the UpdatedTime column, and some queries will need to be altered.
Note as well that you can actually avoid converting all of your queries if you create a view of the table that simply returns the most recent entry for each of the records; you end up with a table which maintains historical data transparently, and a view which looks like a regular table without the changelogging.
[Late post but it adds two techniques not already mentioned here]
Reading transaction log – if your database is in full recovery mode then transaction log stores a lot of useful information that can be used to see history of each row. Downside is that this is not supported by default. You can try using undocumented functions DBCC LOG or fn_dblog or third party tool such as ApexSQL Log
Using Change Data Capture - Change data capture essentially does the same thing like shown above but it’s more streamlined and a bit easier to use. Unfortunately this is only available in enterprise edition.
Both of these can solve the problem of allowing updating and deleting because you can’t really change what’s written in transaction log.
A totally different approach is to only have an audit log. You then use this to build the most current version of your data. You create "checkpoints" periodically or using caching to speed this up.
There is a presentation about somebody using this technique: http://www.infoq.com/presentations/greg-young-unshackle-qcon08. The big advantage here is that since you only have the audit log you'll be quite confident that your audit trail is correct.
I've never tried this and it seems pretty complicated ... but something to think about.
发布评论
评论（5）
使用“仅插入数据库”
基本思想是永远不更新或删除数据。
每个表都有 2 个日期时间列从和到。
它们以 null 值开头（从时间开始到时间结束）。
当您需要“更改”行时，您添加一个新行，同时将上一个行中的 to 更新为到 Now 的行以及您要添加到 Now 的行中的来自。
您可以通过其中包含 where to = null 的视图从表中读取数据。
此方法还可以让您了解数据库在任何时间点的状态。
编辑
只是为了回应评论而澄清：序列将由表的主键给出，这将是一个自动增量数字。
Use "Insert Only Databases"
The basic idea is that you never update or delete data.
Each table has 2 datetime columns from and to.
They start with the value null in each (beginning of time to end of time)
When you need to "change" the row you add a new row, at the same time you update the to in the previous row to Now and the from in the row you are adding to Now.
You read data out of the table via a view that has a where to = null in it.
This method also gives you a picture of the state of your database at any point in time.
EDIT
Just to clarify in response to the comment: The sequence would be given by the primary key of the table, which would be an autoincrement number.
使用“仅插入”数据库，如 Shiraz Bhaji 所描述，但您可以使用更简单的技术。 对于需要维护审核数据的每个表，只需增加一个更新时间列，默认为现在。 当您对记录进行更改时，只需插入所有数据即可，而不是更新； UpdatedTime 列将获取当前时间。
请注意，此方法意味着您必须打破或重新考虑您的 UNIQUE 约束； 您可以保留主键，但唯一性变成了主键和 UpdatedTime 的组合。
此技术的优点是为您提供表中每条记录的已知历史数据范围（如果每条记录是 WHERE TimeOfInterest > UpdatedTime ORDER BY UpdatedTime DESC 的记录中的 TOP 1，则该记录在给定时间内有效），并且具有较低的值开销（只是表上的一列）。 它也非常适合从不使用此方法的表进行转换，使用简单的 ALTER TABLE 来添加单个列（您可以一致地命名）。 然后，您只需更改 UNIQUE 约束以使用当前约束和 UpdatedTime 列的组合，并且需要更改一些查询。
另请注意，如果您创建一个仅返回每条记录的最新条目的表视图，则实际上可以避免转换所有查询； 您最终会得到一个透明地维护历史数据的表，以及一个看起来像没有更改日志的常规表的视图。
Use an "insert only" database, as described by Shiraz Bhaji, but you can use a simpler technique. For each table that you need to maintain audit data for, just have an additional column for Updated Time, defaulting to now. When you make a change to a record, instead of updating, just do an insert with all your data; the UpdatedTime column will get the current time.
Note that this method means you have to break or reconsider your UNIQUE constraints; you can keep a primary key, but the uniqueness becomes a composite of your primary key and your UpdatedTime.
This technique has the advantage of giving you a known range of historical data for each record on the table (each record is valid for a given time if it is the TOP 1 of records WHERE TimeOfInterest > UpdatedTime ORDER BY UpdatedTime DESC) with a low overhead (just a single column on the table). It's also quite amenable to conversion from tables not using this method, with a simple ALTER TABLE to add a single column (which you can name consistently). Then you just need to alter your UNIQUE constraints to use a composite of their current contraints and the UpdatedTime column, and some queries will need to be altered.
Note as well that you can actually avoid converting all of your queries if you create a view of the table that simply returns the most recent entry for each of the records; you end up with a table which maintains historical data transparently, and a view which looks like a regular table without the changelogging.
[迟到的帖子，但它添加了此处尚未提及的两项技术]
读取事务日志 - 如果您的数据库处于完全恢复模式，则事务日志会存储许多有用的信息，这些信息可以用于查看每一行的历史记录。
缺点是默认情况下不支持。 您可以尝试使用未记录的函数 DBCC LOG 或 fn_dblog 或第三方工具，例如 ApexSQL Log
使用更改数据捕获 - 更改数据capture 本质上与上面所示的功能相同，但它更加精简并且更易于使用。 不幸的是，这仅在企业版中可用。
这两者都可以解决允许更新和删除的问题，因为您无法真正更改事务日志中写入的内容。
[Late post but it adds two techniques not already mentioned here]
Reading transaction log – if your database is in full recovery mode then transaction log stores a lot of useful information that can be used to see history of each row.
Downside is that this is not supported by default. You can try using undocumented functions DBCC LOG or fn_dblog or third party tool such as ApexSQL Log
Using Change Data Capture - Change data capture essentially does the same thing like shown above but it’s more streamlined and a bit easier to use. Unfortunately this is only available in enterprise edition.
Both of these can solve the problem of allowing updating and deleting because you can’t really change what’s written in transaction log.
一种完全不同的方法是仅拥有审核日志。 然后，您可以使用它来构建最新版本的数据。 您可以定期创建“检查点”或使用缓存来加快速度。
有一个关于有人使用这种技术的演示： http://www.infoq.com /presentations/greg-young-unshackle-qcon08。 这里的一大优点是，由于您只有审核日志，您将非常有信心您的审核跟踪是正确的。
我从来没有尝试过这个，它看起来很复杂......但需要考虑一下。
A totally different approach is to only have an audit log. You then use this to build the most current version of your data. You create "checkpoints" periodically or using caching to speed this up.
There is a presentation about somebody using this technique: http://www.infoq.com/presentations/greg-young-unshackle-qcon08. The big advantage here is that since you only have the audit log you'll be quite confident that your audit trail is correct.
I've never tried this and it seems pretty complicated ... but something to think about.
看看我对另一个数据库日志问题的回答是否包含您需要的信息。 在这里找到它...
历史表的优点、缺点和陷阱 - 使用触发器、存储过程或在应用程序级别
See if my answer to another database logging question contains the information you need. Find it here...
History tables pros, cons and gotchas - using triggers, sproc or at application level