问题描述
对于我正在从事的项目，系统要求我创建对记录所做的所有更改的审核跟踪.这是我第一次必须创建审计跟踪，因此我一直在对该主题进行大量研究.
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.
如果您的数据库不支持触发器，则可能您正在使用某种持久性或数据库层.这也是放置这种逻辑的好地方，因为您再次最小化了 normal 应用程序代码和审核代码之间的任何依赖关系.
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:至于显示指标:我不会在存储实际值的表中创建布尔字段. (这将导致 normal 应用程序代码和 audit Trail 代码之间存在各种依赖关系.)
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.
我维护的一些大型Enterprisy应用程序大致使用以下结构:
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)
这篇关于我将如何使用审核跟踪显示曾经被编辑过的字段?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！