Some thoughts and questions on this subject.

I've done something similar. I've also added a DELETE trigger, to mark in the audit log when and if the data got deleted, which has proven very useful on occasion.

There would seem to be two tactics to this: either write the data to the log as it is being changed, or as it is being entered. consider this set of events:

Insert 1, A

Update to 1, B

Update to 1, C

With "Audit on change", the audit table would only be added to on the updates, and only the data being overwritten would be added. At the end of the actions above, it'd have (1,A) and (1,B). With "Audit on create", the audit table would be added to for the insert and the updates, with the data being added or inserted. At the end of the series above, it'd have (1,A), (1,B), and (1,C).

The big advantage of "Audit on change" are that you store less data; the big advantage of "Audit on create" is that you'd have the history of all the data in one place.

So, which is better? What tactic would or does anyone use?

A last issue is data archive and purge. Since this data piles up quickly over time, presumably you archive and purge it out of the database regularly and consistantly, right?

Philip