如何保留对表的更改的审计/历史记录
我被要求创建一个简单的 DataGrid 样式的应用程序来编辑数据库的单个表，这很容易.但请求的一部分是创建对所做更改、谁进行更改以及日期/时间的审计跟踪.
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#、连接到 SQL Server 2005 的 ADO.NET、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.
- 编码您的数据访问层.
- 在数据库本身中使用触发器.
两者各有优缺点.有些人更喜欢一种.这通常取决于您可以预期的应用程序类型和数据库使用类型.
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 层中执行此操作，则完全取决于您.您只需将代码添加到保存到数据库的每个方法中，以同时保存更改日志.这个审计代码可以在你的 DA 层代码中，或者甚至在你的数据库中的存储过程中，如果你对所有东西都使用存储过程.基本上前提是相同的，任何时候对数据库进行更改，都要记录该更改.
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.
如果你想走触发器路线，你可以为每个表编写自定义触发器，或者设计一个更通用的触发器，在很多表上都一样.查看 这篇关于审计触发器的文章.这通过在发生更改时触发触发器来工作，并且触发器记录更改.请记住，如果您想审核 SELECT 语句，则不能使用触发器，您必须在代码/存储过程审核中执行此操作.还值得记住的是，根据您的数据库，触发器可能不会在所有情况下都触发.例如，大多数数据库在 TRUNCATE 语句期间不会触发触发器.检查您的触发器是否在您需要审核的任何情况下都被触发.
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.
相关文章