Have you ever worked on a data-driven application where it was essential all changes to the system's underlying data could be examined by an auditor at some later point in time? In medical, university, governmental, and, increasingly, corporate settings, it is becoming increasingly important for legal reasons that one can go back into the system and see precisely how the data changed over time and (most importantly) by whom.
If you've ever needed to create such an auditing application, what techniques have you used? When I've needed such functionality I've always done the following:
- For each database table X that requires an audit trail, create a table named XAudit that has the exact same schema as X but with four additional fields:
- XAuditID - a primary key, IDENTITY field
- Deleted - a bit field that defaults to 0 that indicates if the record has been deleted.
- CreatedOn - a datetime field that defaults to the current date and time (getdate()).
- CreatedBy - a field that records who changed the data. If you are having the users log directly onto the database to perform the edits, you can use the SUSER_SNAME() function to get the username. If you are having all users come through some standard account, you'll need to record this information in the X table so that it translates over smoothly to the audit table.
Note: you may already be tracking the last time and user that changed a record's data, and therefore these last two fields would be captured in table X and therefore not need to be additional fields added to the audit table.
- Create a trigger on X for UPDATE and INSERT that inserts the contents from the inserted table into XAudit.
- Create a trigger on X for DELETE that inserts the contents from the deleted table into XAudit, putting in a value of 1 into the audit table's Deleted field.
That's all there is to it! At that point you have a complete audit trail showing creation, editing, and deletion of all records, including when the change was made and by whom.
If you keep an audit trail using a different technique, or if there's a better way than how I do it, I'd be interested in learning more - leave a comment.