如何保持变更的审计/历史记录表 [英] How to keep an audit/history of changes to the table

问题描述

我被要求创建一个简单的数据网格式的应用程序来编辑数据库的一个表，这是很容易做到。但请求的一部分是创建所做的更改，谁使他们的审计跟踪，以及日期/时间。

I've been asked to create a simple DataGrid-style application to edit a single table of a database, and that's easy enough. But part of the request is to create an audit trail of changes made, who made them, and the date/time.

怎么可能你解决这种事情？

How might you solve this kind of thing?

（我将使用VS2008中的C＃，ADO.NET连接到SQL Server 2005，WPF和Xceed的DataGrid中，如果这有什么差别。）

(I'll be using C# in VS2008, ADO.NET connected to SQL Server 2005, WPF and Xceed's DataGrid, if it makes any difference.)

推荐答案

有创建审核记录的两种常用方法。

There are two common ways of creating audit trails.

C本数据访问层
1. 在$ C $。
2. 在数据库本身使用触发器。

有优点和缺点两者。有些人preFER一个比其他。这是经常到应用程序的类型和数据库的类型使用，你可以期望。

There are advantages and disadvantages to both. Some people prefer one over the other. It's often down to the type of app and the type of database use you can expect.

如果你这样做你的DA层是pretty的多少取决于你。你只需要添加code到每一个保存到数据库还保存日志的变化方式。该审计code可能是您的DA层code，甚至可以在你的数据库中存储的特效，如果你使用的是存储的特效做的一切。本质上，premise是一样的，任何时候你更改了数据库，记录这一变化。

If you do it in your DA layer it's pretty much up to you. You just need to add code to every method that saves to the database to also save a log of the changes. This auditing code could be in your DA layer code, or even in your stored procs in your database if you are using stored procs for everything. Essentially the premise is the same, any time you make a change to the database, log that change.

如果你想下去触发器的路线，你可以编写自定义触发每个表，或时尚更通用的触发器，它的工作原理相同的大量表。看看<一href="http://weblogs.asp.net/jgalloway/archive/2008/01/27/adding-simple-trigger-based-auditing-to-your-sql-server-database.aspx">this审计文章触发。这通过激发触发器无论何时进行了更改，并且所述触发器记录的更改。记住，如果你要审核的SELECT语句，你不能使用触发器，你就必须做到这一点，在code /存储过程审计。这也是值得记住，这取决于你的数据库，触发器可能不会触发在所有情况下。例如，大多数数据库不火时截短语句触发器。检查你的触发器被解雇的，你需要审核任何情况下。

If you want to go down the triggers route, you can write custom triggers for each table, or fashion a more generic trigger that works the same on lots of tables. Check out this article on audit triggers. This works by firing of triggers whenever a change is made, and the triggers log the changes. Remember that if you want to audit SELECT statements, you can't use triggers, you'll have to do that with in code/stored proc auditing. It's also worth remember that depending on your database, triggers may not fire in all circumstances. For example, most databases don't fire triggers during TRUNCATE statements. Check that your triggers get fired in any case that you need auditing.

另外，你也可以看看使用服务代理做一个专用的机器异步审核。这是更复杂，需要多一点的配置来设置的。

Alternately, you could also take a look at using the service broker to do async auditing on a dedicated machine. This is more complex and takes a bit of configuring to set up.

而且，任何方法你做到这一点，你需要决定的格式的审计日志会抓住。通常你会保存该记录在数据库中，但你可以只保存在一个日志文件或任何适合您的需求。您可以使用记录所有修改单审核表，或者你可以有每个主表的审核表被审计。对于大规模的实现，你甚至可以考虑将审核表中的一个完全独立的数据库。如果您登录到一个表，这是常见的有一个变型字段，它表示如果经审计的改变是插入，更新或删除变化的风格，伴随着改变的数据，用户谁做的变化和日期/时间进行了更改。不要忘了，包括旧的和新的数据进行更新式的变化。

Which ever way you do it you need to decide on the format the audit log will take. Normally you would save this log in your database, but you could just save it in a log file or whatever suits your requirements. You could use a single audit table that logs all changes, or you could have an audit table per main table being audited. For large scale implementations you could even consider putting the audit tables in a totally separate database. If your logging into a table, it's common to have a "change type" field which indicates if the audited change was an insert, update or delete style of change, along with the changed data, user who made the change and the date/time the change was made. Don't forget to include the old and new data for update style changes.

这篇关于如何保持变更的审计/历史记录表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！