维护审核实体的审计日志，这些实体分布在多个表中 [英] Maintaining audit log for entities split across multiple tables

问题描述

我们有一个实体，分布在5个不同的表格中.这些表中的3个中的记录是强制性的.其他两个表中的记录是可选的(基于实体的子类型).

We have an entity split across 5 different tables. Records in 3 of those tables are mandatory. Records in the other two tables are optional (based on sub-type of entity).

其中一个表被指定为实体主控.其他四个表中的记录由master的唯一ID键输入.

One of the tables is designated the entity master. Records in the other four tables are keyed by the unique id from master.

在每个表上都存在更新/删除触发器之后，对记录的更改会将历史记录(从触发器内部的已删除表中保存)保存到相关的历史记录表中.每个历史记录表都包含相关的实体字段和一个时间戳.

After update/delete trigger is present on each table and a change of a record saves off history (from deleted table inside trigger) into a related history table. Each history table contains related entity fields + a timestamp.

因此，实时记录始终位于实时表中，历史记录/更改位于历史记录表中.可以根据时间戳列对历史记录进行排序.显然，时间戳列在历史记录表之间是不相关的.

So, live records are always in the live tables and history/changes are in history tables. Historical records can be ordered based on the timestamp column. Obviously, timestamp columns are not related across history tables.

现在，对于更困难的部分.

Now, for the more difficult part.

1. 最初将记录插入单个事务中.一次记录将写入3或5条记录.
2. 5个表中的任何一个或全部可能会发生个别更新.
3. 所有记录都作为单个事务的一部分进行更新.同样，一次交易将更新3或5条记录.
4. 数字2可以重复多次.
5. 数字3可以重复多次.

该应用程序应该基于仅记录为单个事务的记录显示时间点历史记录列表(仅限点1,3和5)

The application is supposed to display a list of point in time history entries based on records written as single transactions only (points 1,3 and 5 only)

我目前在算法上遇到问题，该算法将仅基于时间戳数据来检索历史记录.

I'm currently having problems with an algorithm that will retrieve historical records based on timestamp data alone.

添加一个HISTORYMASTER表来保存有关事务的额外信息似乎可以部分解决该问题.每次交易之前，新记录都会添加到HISTORYMASTER中.在事务期间，新的HISTORYMASTER.ID将保存到每个实体表中. 可以通过选择特定HISTORYMASTER.ID的第一条记录来检索时间点的历史记录(按时间戳记排序)

Adding a HISTORYMASTER table to hold the extra information about transactions seems to partially address the problem. A new record is added into HISTORYMASTER before every transaction. New HISTORYMASTER.ID is saved into each entity table during a transaction. Point in time history can be retrieved by selecting the first record for a particular HISTORYMASTER.ID (ordered by timestamp)

对于跨多个表的实体，是否还有其他更好的方法来管理基于AFTER(更新，删除)触发器的审计表?

Is there any more optimal way to manage audit tables based on AFTER (UPDATE, DELETE) TRIGGERs for entities spanning multiple tables?

推荐答案

您的HistoryMaster似乎与我们在一个系统中处理多个相关项目的历史类似.通过单点挂起历史记录表中的所有相关更改，可以轻松创建一个以历史记录母版为中心并附加相关信息的视图.它还允许您在不需要审核的历史记录中不创建记录.

Your HistoryMaster seems similar to how we have addressed history of multiple related items in one of our systems. By having a single point to hang all the related changes from in the history table, it is easy to then create a view that uses the history master as the hub and attached the related information. It also allows you to not create records in the history where an audit is not desired.

在我们的例子中，主表称为EntityAudit(其中实体是保留的主"项目)，所有数据都存储了与审计相关的EntityHistory表.在我们的案例中，我们将数据层用于业务规则，因此很容易将审计规则插入数据层本身.我认为，当且仅当所有修改都使用该数据层时，数据层才是进行此类跟踪的最佳点.如果您有多个使用不同数据层的应用程序(或根本没有)，那么我怀疑创建主记录的触发器几乎是唯一的方法.

In our case the primary tables were called EntityAudit (where entity was the "primary" item being retained) and all data was stored EntityHistory tables related back to the Audit. In our case we were using a data layer for business rules, so it was easy to insert the audit rules into the data layer itself. I feel that the data layer is an optimal point for such tracking if and only if all modifications use that data layer. If you have multiple applications using distinct data layers (or none at all) then I suspect that a trigger than creates the master record is pretty much the only way to go.

如果您没有要在审计中跟踪的其他信息(例如，我们跟踪进行更改的用户，例如，不在主表中的用户)，那么我会考虑将多余的主"记录本身上的审核ID.您的描述似乎并不表示您对单个表的微小更改感兴趣，而只是对更新整个实体集的更改感兴趣(尽管我可能会念念它).不过，如果您不关心较小的修改，我只会这样做.在我们的案例中，我们需要跟踪所有更改，甚至是相关记录.

If you don't have additional information to track in the Audit (we track the user who made the change, for example, something not on the main tables) then I would contemplate putting the extra Audit ID on the "primary" record itself. Your description does not seem to indicate you are interested in the minor changes to individual tables, but only changes that update the entire entity set (although I may be miss reading that). I would only do so if you don't care about the minor edits though. In our case, we needed to track all changes, even to the related records.

请注意，使用审核/主表的优势在于，与源表相比，您对历史记录表进行了最小更改:单个AuditID(在我们的示例中为指导，尽管自动编号在非分布式数据库中会很好).

Note that the use of an Audit/Master table has an advantage in that you are making minimal changes to the History tables as compared to the source tables: a single AuditID (in our case, a Guid, although autonumbers would be fine in non distributed databases).

这篇关于维护审核实体的审计日志，这些实体分布在多个表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！