Microsoft® SQL Server® 2008 Bible

Chapter 53. Data Audit Triggers

IN THIS CHAPTER

• Creating a robust data-audit trail

• Rolling back changes

• Undeleting rows

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

The section "Data Architecture" in Chapter 2 lists data auditing as a key contributor toward the security and integrity database objectives.

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