1

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?

Casey Wilkins
  • 2,505
  • 2
  • 22
  • 28
  • I'm not sure I understand. This seems to be working as intended. There shouldn't be an audit records for `tblOrderLines` since nothing was changed, right? Did you want an audit record for `tblOrderLines` for some reason? What would it say? – IAmTimCorey May 14 '11 at 03:20
  • I'm not explaining it well enough. If I need to go back to some point in time and see a snapshot of an Order, I need the appropriate Order Lines to go with it. I load up an Audit record for an Order... how do I tell which OrderLine Audit record goes with it so that the state of the entire aggregate is valid? – Casey Wilkins May 14 '11 at 03:38
  • OK, I understand what you are saying. Look at the answer from @YetAnotherUser for the answer. Basically, you are rolling back to a point in time, not trying to link up the archive tables to each other. – IAmTimCorey May 14 '11 at 03:41
  • @BiggsTRC Read my comment to him regarding not knowing T-1. I probably didn't explain it well enough, but what I'm really looking for is being able to recreate every state the aggregate has been in since it was created. That would allow me to see where a problem originated, and potentially fix the data. Hope that makes sense, I really shouldn't post questions this late after racking my brain all day :) – Casey Wilkins May 14 '11 at 03:48

2 Answers2

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 -

Community
  • 1
  • 1
YetAnotherUser
  • 8,490
  • 3
  • 36
  • 52
  • I'd agree with that, but what if I don't know time T-1? For instance, I know something is wrong with an Order (a user entered bad data, etc.). I need to see a list of state changes that the aggregate has gone through so I can see where the problem occurred. Can I recreate every state of the aggregate since it's creation from the two audit tables? Sorry if this isn't making sense, it's hard to explain exactly what I'm thinking. – Casey Wilkins May 14 '11 at 03:43
  • @Casey : Yes you should be able to recreate every state. Remember in your scenario you'll be using the audit tables to recreate main tables, but for look at the data at a specific time you'll look at your main tables. – YetAnotherUser May 14 '11 at 03:50
  • @Casey : I'm assuming you'll have time stamps in both your main table and audit table to put these on a single timeline. What would probably be helpful is if you try to create a scenario in say excel and see how data changes and what is the view of different tables. It would clarify a lot of your doubts. – YetAnotherUser May 14 '11 at 03:52
0

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...

Denis de Bernardy
  • 67,991
  • 12
  • 114
  • 140