There are a number of ways to track data changes in SQL Server databases, many of which include table triggers to log the changes. In this post I provide a streamlined approach to setting up those triggers and keeping them up to date.
A number of years ago I inherited an application that had a data auditing solution. I am not sure of the origins of the solution, but it is similar to other ideas out on the Internet. The main idea is an audit table to hold the history, and triggers on all the tables to log the changes. This worked well for us, but as we made changes to the application it was challenging to keep the triggers up to date. To solve that challenge I wrote a stored procedure called sp_build_triggers (see link below) to generate the trigger creation scripts based on the Information Schema views in the database. After making structure changes to a database (adding/removing columns or tables), I run sp_build_triggers, then copy the text it generates to a new query window and run it on the database. This updates all the triggers previously created by the script.
This solution is geared toward custom applications where you can have the database calls send the date and the user making the change, as well as modifying the database to include columns for these values. If you do not have that level of control, this solution could be modified to remove the user ID fields, which would then give a history of what changes were made when. It also works best with single column primary keys. It will function with multi-value keys, but only the first column will be listed in the audit table. The solutions I use this with primarily use uniqueidentifiers and integers.
This solution only logs updates and deletes. Inserted records are tracked with the Created and CreatedByID columns. To be clear, this solution does not cover auditing events, logins, structure, or permission changes. SQL Server provides a built-in method for that. (Google “SQL Server Audit Specification”)
Once the above setup items are complete you are ready to run “exec sp_build_triggers”. Copy the text output, paste it in a new query window, and run it to create the triggers. Now you can change some data and watch it appear in the audit table.
Tech Talk Live is the only conference of its kind in the region specifically designed for IT pros in education.
1020 New Holland Avenue, Lancaster, PA 17601