我将如何使用审计跟踪来显示哪些字段曾经被编辑过? [英] How would I use an audit trail to display which fields have ever been edited?
问题描述
对于我正在进行的一个项目，我被要求创建对记录所做的所有更改的审计跟踪.这是我第一次必须创建审计跟踪，所以我一直在对这个主题进行大量研究.
For a project I am working on, I have been asked to create an audit trail of all changes that have been made to records. This is the first time I have had to create an audit trail, so I have been doing a lot of research on the subject.
该应用程序将使用 PHP/MSSQL 开发，并且流量较低.
The application will be developed in PHP/MSSQL, and will be low-traffic.
从我的阅读中，我几乎决定拥有一个审计表并使用触发器来记录表中的更改.
From my reading, I have pretty much decided to have an audit table and use triggers to record the changes in the table.
在应用中显示的两个要求如下:
The two requirements for display in the application are as follows:
能够查看对字段所做的所有更改的日志(我几乎知道如何做到这一点)
Be able to see a log of all changes made to a field (I pretty much know how to do this)
在应用程序中查看记录时，能够看到记录中任何曾经更改过的字段旁边的指示符(可能还有其他信息，如上次更改的日期).
Be able to see, when viewing a record in the application, an indicator next to any field in the record that has ever been changed (and possibly other info like the date of the last change).
第 2 项是目前让我感到悲伤的一项.如果没有对每个字段进行单独的查询(或需要很长时间才能执行的很长的嵌套查询)，是否有人对执行此操作的最佳方法有任何建议?(我曾想过为表中的每个字段添加一个额外的ModifiedFlag"字段，如果该字段曾经被编辑过，它将充当布尔指示符，但这似乎是一个很大的开销.
Item #2 is the one that is currently giving me grief. Without doing a separate query for each field (or a very long nested query that will take ages to execute), does anyone have suggestions for an optimal way to do this? (I have thought of adding an extra "ModifiedFlag" field for each field in the table, that will act as boolean indicator if the field has ever been edited, but that seems like a lot of overhead.
推荐答案
我会尽可能将审计信息与实际域信息分开处理.
I would treat the audit information separately from the actual domain information as much as possible.
要求 1:我认为您将创建额外的审计表来记录更改.Eric 的建议很好，使用 SQL 数据库中的触发器创建审计信息.这样您的应用程序就不需要知道审计逻辑.
Requirement #1: I think you will create additional audit tables to record the changes. Eric suggestion is a good one, creating the audit information using triggers in the SQL database. This way your application needs not be aware of the audit logic.
如果您的数据库不支持触发器，那么您可能正在使用某种持久性或数据库层.这也是放置这种逻辑的好地方，因为您再次最小化普通应用程序代码和审计代码之间的任何依赖关系.
If your database does not support triggers, then perhaps you are using some kind of persistence or database layer. This would also be a good place to put this kind of logic, as again you minimize any dependencies between normal application code and the audit code.
要求 2:至于显示指标:我不会在存储实际值的表中创建布尔字段.(这会导致普通应用程序代码和审计跟踪代码之间存在各种依赖关系.)
Requirement #2: As for showing the indicators: I would not create boolean fields in the table that stores the actual. (This would cause all sorts of dependencies to exist between your normal application code and your audit trail code.)
我会尽量让负责显示表单的代码也负责显示字段级别的审计数据.这将导致查询开销，但这是显示这一额外信息层的成本.也许您可以通过将元数据添加到审计信息中以便于检索，从而最大限度地减少数据库开销.
I would try to let the code responsible for displaying the form also be responsible for showing audit data on field level. This will cause query overhead, but that is the cost for displaying this extra layer of information. Perhaps you can minimize the database overhead by adding metadata to the audit information that allows for easy retrieval.
我维护的一些大型企业应用程序大致使用以下结构:
Some big Enterprisy application that I maintain uses roughly the following structure:
- 对应于表中记录更改的更改头表.
字段:
changeId, changeTable, changedPrimaryKey, userName, dateTime
- 改变字段对应的一个改变字段表.
- A change field table corresponding to a field that is changed.
字段:
changeId, changeField, oldValue, NewValue
示例内容:
更改标题:
'1', 'BooksTable', '1852860138', 'AdamsD', '2009-07-01 15:30'
更改项目:
'1', 'Title', 'The Hitchhiker's Guide to the Gaxaly', 'The Hitchhiker's Guide to the Galaxy'
'1', 'Author', 'Duglas Adasm', 'Douglas Adams'
这种结构既可以轻松查看审计跟踪，也可以轻松检索以显示所需的指标.一个查询(Header 和 Items 表中的内部联接)足以检索所有信息以显示在单个表单中.(或者甚至是一个表格，当你有一个显示 Id 的列表时)
This structure allows both easy viewing of audit trails as well as easy retrieval for showing the desired indicators. One query (inner join in the Header and Items table) would be enough to retrieve all information to show in a single form. (Or even a table when you have a list of shown Id's)
这篇关于我将如何使用审计跟踪来显示哪些字段曾经被编辑过?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
