Auditing DB Changes

It seems to me there are two common strategies for doing DB audit trails via a trigger:
1. On an update to a row, duplicate that row in another table withidentical rows, except for perhaps the extra columns which representchange date and changed by. Eg. When there is an update to the Customertable, record the changes in Customer_Audit.
2. On an update to a row, check which fields were updated. Then in acommon audit table record the table, row ID, field, previous value andnew value of the field.
I'm wondering about the pros and cons of each. More specifically, dothe pros and cons change if you are using an O/R mapper (I'm usingNHibernate.)
Some thoughts on method 1 . It seems nice for an O/R mapper, since youcould have, say, a CustomerAudit Object inherit from your Customerobject and just add the properties change date and changed by. Aproblem with this is you're going to add a whole lot of objects - onefor each object which you want to audit. Another drawback is that itcould be difficult to generate a history for a particular propertywhich was updated. Let's say I want to see the history of changes tothe customer's status. I have to load a collection of CustomerAuditobjects (which could be costly). Then I have to iterate through themand compare the status properties to generate a history of statuses.This is a pretty labor instensive method if you compare it to method 2,where the change is recorded by field, not row.
Some thoughts on method 2. It's nice since the changes are by field,not row, which (as above) makes generating a history easy. On the otherhand, you can never have a snapshot of a particular object at aparticular point in time. Moreover, I'm not sure how foreign keys wouldbe handled elegantly. I record that customer.statusID changes from 3 to6. I'd have to do a seperate join to the customerstatus table to getmeaning for 3 and 6 (which method 1 would do automatically).
Thoughts? Any preferred way to do this with an O/R mapper?
Thanks

nick7272:

It seems to me there are two common strategies for doing DB audit trails via a trigger:

1. On an update to a row, duplicate that row in another table with identical rows, except for perhaps the extra columns which represent change date and changed by. Eg. When there is an update to the Customer table, record the changes in Customer_Audit.

2. On an update to a row, check which fields were updated. Then in a common audit table record the table, row ID, field, previous value and new value of the field.
...

Nick7272 --

I am surprised there were no replies to your post.

You have the gist of the standard analysis above. Well done.

I want to pick up the topic because I am about to build yet another auditing system.

I think that there is at least one other option in addition to the ones you mention above. It is similar to similar to option 1. Note that in option 1, a developer must propogate schema changes in the tables-to-be-audited into the tables-doing-the-auditing. That can be a chore. I have implemented option 1 and it works OK for < 50 tables or so. More than that and it could be a problem if the schema changes. I am thinking that Option 3 (as yet never implemented by me) one could avoid the need for schema synchronization by using XML. Serialize an object to XML and store that. Sure, an audit recored for an object saved 1 year ago might have a different schema from an object saved 1 day ago, but all of the data would be there. I would be ugly; but, it would "mineable". It would tough to search; but, it would require no schema synchronizatoin. And so on. The problem would come from the complexity of the objects being serialized. Maybe the object could have a custom SerializeEx method that would be a lightweight version of the object, with just the necessary field names and data. Maybe. Anyway, I just wanted to mention this.

Regarding your post, I am wondering...

Since you made the post, have you discovered anything new?

What did you choose for a solution?

Please advise.

Thank you.

-- Mark Kamoski