How do you create an audit trail for aggregate roo

Question

I have read several articles online as well as several answers on StackOverflow about creating an audit trail for a database driven application. It seems that the most popular solution is to create an audit table for the table in question and use triggers to insert an audit record into the audit table.

I can see how this would work well for simple entities whose data is contained in one table.

What about aggregate roots that contain children?

Example:

Order is an aggregate root containing many Order Lines, each with their own table in the database. Assume each also has an audit table in the database that receives updates via triggers when the original table is changed:

tblOrders --> Trigger --> tblOrdersAudit
tblOrderLines --> Trigger --> tblOrderLinesAudit

Now, suppose we change something about an Order, but make no changes to any of its Order Lines. tblOrders is updated as a result, and a trigger inserts a new audit record reflecting the changes to tblOrdersAudit. However, no changes have been made to tblOrderLines and as a result there is no matching audit record in tblOrderLinesAudit.

Some time later I need to see the an earlier state of the Order, perhaps to rollback the data. How do we match up the audit records?

Answer 1

In case of roll back wouldn't you be doing it per table basis? Assume only change ever made to the database was since time T-1 was updation of tblOrders. In this case

1. tblOrders would be rolled back to time T-1: Values from audit will be used to bring tblOrders back to how it was at T-1.

2. tblOrdersLines would be rolled back to time T-1: There is no entry in tblOrdersLineAudit and hence nothing will be updated.

At the end you have your tables are in the state they were at T-1.

Few links for more info -

• How to version control a record in a database
• Database history for client usage
• Using Triggers to Track Database Action History

Answer 2

Some time later I need to see the an earlier state of the Order, perhaps to rollback the data. How do we match up the audit records?

Not easy, as you correctly identified.

Personally, I store a copy of the whole aggregate when I need to revisit snapshots. Put another way, on insert/update/delete to orders or order lines or any other associated tables, I log that order + each order line + each related line in other associated tables.

It's not efficient from a storage standpoint (even though I tend to store the final snapshot per transaction, rather than each change), nor is it ideal from a performance standpoint, but it gets the job done...