Adding simple trigger-based auditing to your SQL Server database
How do you track changes to data in your database? There are a variety of supported auditing methods for SQL Server, including comprehensive C2 security auditing, but what do you do if you're solving a business rather than a security problem, and you're interested in tracking the following kinds of information:
- What data has been updated recently
- Which tables have not been updated recently
- Who modified the price of Steeleye Stout to $20 / unit, and when did they do it?
- What was the unit price for Steeleye Stout before Jon monkeyed with it?
There are a number of ways to design this into your solution from the start, for example:
- The application is designed so that all changes are logged
- All data changes go through a data access layer which logs all changes
- The database is constructed in such a way that logging information is included in each table, perhaps set via a trigger
What if we're not starting from scratch?
But what do you do if you need to add lightweight auditing to an existing solution, in which data can be modified via a variety of direct access methods? When I ran into that challenge, I decided to use Nigel Rivett's SQL Server Auditing triggers. I read about some concern with the performance impact, but this database wasn't forecasted to have a high update rate. Nigel's script works by adding a trigger for INSERT, UPDATE, and DELETE on a single table. The trigger catches data changes, then saves out the information (such as table name, the primary key values, the column name that was altered, and the before and after values for that column) to an Audit table.
I needed to track every table in the database, though, and I expected the database schema to continue to change. I was able to generalize the solution a bit, because the database convention didn't use any no compound primary keys. I created the script listed below, which loops through all tables in the database with the exception of the Audit table, of course, since auditing changes to the audit table is both unnecessary and recursive. I'm also skipping sysdiagrams; you could include any other tables you don't want to track to that list as well.
The nice thing about the script I'm including below is that you can run it after making some schema changes and it will make sure that all newly added tables are included in the change tracking / audit, too.
Here's an example of what you'd see in the audit table for an Update followed by an Insert. Notice that the Update shows type U and a single column updated, while the Insert (type I) shows all columns added, one on each row:
While this information is pretty unstructured, it's not difficult to run some useful reports. For instance, we can easily find things like
- which tables were updated recently
- which tables have not been updated in the past year
- which tables have never been updated
- all changes made by a specific user in a time period
- most active tables in a time period
While it's not as easy, it's possible to backtrack from the current state to determine the state of a row in a table at a certain point in time. It's generally possible to dig out the state of an entire table at a point in time, but a change table isn't a good a fit for temporal data tracking - the right solution there is to start adding Modified By and Modified On columns to the required tables.
Note that we're only tracking data changes here. If you'd like to track schema changes, take a look at SQL Server 2005's DDL triggers.
Enough talking, give us the script!
Sure. I'll repeat that there are some disclaimers to the approach - performance, it'll only track changes to tables with a primary key, etc. If you want to know more about the trigger itself, I'd recommend starting with Nigel's article. However, it worked great for our project.