database 用于捕获审计跟踪的数据库设计的想法

Ideas on database design for capturing audit trails

提问by Greens

How can I maintain a log of the data in my DB?

如何维护数据库中数据的日志？

I have to maintain a log of every change made to each row. That means that I can't allow DELETEand UPDATEto be performed.

我必须维护对每一行所做的每次更改的日志。这意味着我不能允许DELETE和UPDATE被执行。

How can I keep such a log?

我怎样才能保留这样的日志？

回答by Shiraz Bhaiji

Use "Insert Only Databases"

使用“仅插入数据库”

The basic idea is that you never update or delete data.

基本思想是永远不要更新或删除数据。

Each table has 2 datetime columns fromand to.

每个表都有 2 个日期时间列from和to。

They start with the value null in each (beginning of time to end of time)

它们以每个值 null 开始（从时间开始到时间结束）

When you need to "change" the row you add a new row, at the same time you update the toin the previous row to Now and the fromin 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.

您可以通过其中包含 where to = null 的视图从表中读取数据。

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.

只是为了回应评论而澄清：序列将由表的主键给出，这将是一个自动增量编号。

回答by Herbert Lynch

[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

读取事务日志——如果您的数据库处于完全恢复模式，那么事务日志会存储许多有用的信息，可用于查看每一行的历史记录。缺点是默认情况下不支持。您可以尝试使用未公开的函数 DBCC LOG 或 fn_dblog 或第三方工具，例如ApexSQL Log

Using Change Data Capture- Change data captureessentially 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.

这两者都可以解决允许更新和删除的问题，因为您无法真正更改事务日志中写入的内容。

回答by Paul Sonier

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.

使用“仅插入”数据库，如 Shiraz Bhaji 所述，但您可以使用更简单的技术。对于需要维护审计数据的每个表，只需为更新时间添加一列，默认为现在。当您对记录进行更改时，无需更新，只需插入所有数据即可；UpdatedTime 列将获取当前时间。

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.

请注意，此方法意味着您必须打破或重新考虑您的 UNIQUE 约束；您可以保留主键，但唯一性成为主键和 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.

这种技术的优点是可以为您提供表中每条记录的已知历史数据范围（如果每条记录是记录的前 1 条记录 WHERE TimeOfInterest > UpdatedTime ORDER BY UpdatedTime DESC），则它在给定时间内有效，并且开销很低（只是桌子上的一列）。使用简单的 ALTER TABLE 来添加单个列（您可以一致地命名），也很适合从不使用此方法的表进行转换。然后，您只需更改 UNIQUE 约束以使用其当前约束和 UpdatedTime 列的组合，并且需要更改某些查询。

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.

还要注意的是，如果您创建的表视图只返回每条记录的最新条目，则实际上可以避免转换所有查询；您最终会得到一个透明地维护历史数据的表，以及一个看起来像没有更改日志的普通表的视图。

回答by Dave

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.

有一个关于有人使用这种技术的演示：http: //www.infoq.com/presentations/greg-young-unshackle-qcon08。这里的一大优势是，由于您只有审计日志，因此您可以非常确信您的审计跟踪是正确的。

I've never tried this and it seems pretty complicated ... but something to think about.

我从来没有尝试过这个，它看起来很复杂......但需要考虑一下。

回答by cmdematos.com

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

历史表的优点、缺点和问题 - 使用触发器、sproc 或在应用程序级别