IN THIS CHAPTER
Creating a robust data-audit trail
Rolling back changes
Thinking through auditing complications
My consulting firm once developed a legal compliance/best-practices document-management system for a Fortune 100 company whose law firm was populating the database with regulatory laws. The law firm fell behind on its schedule and claimed that it was unable to enter data for two weeks because of software problems. When a list of the more than 70,000 column-level data changes made during those two weeks was provided from the data-audit trail, the claim vanished.
Data auditing is just a plain good idea.
Although Microsoft has added several auditing technologies, there's still a place for the old trigger/audit table solution. It's still the best way to build a complete audit trail of every value change ever made to a row since the inception of the database.
A trigger based data-audit trail can provide very detailed history of the data, including the following:
All data changes to a row since it was inserted
All data changes made by a specific user last week
All data changes from a certain workstation during lunch
All data changes made from an application other than the standard front-end application
I've seen published methods of auditing data that add a few columns to the table, or duplicate the table, ...