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?