Loading...

Tech Talk Live Blog

Code Generation for SQL Server Data Auditing Triggers

Greg Hess


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”)

Script Requirements and Setup:

  • Create Audit table (see CREATE TABLE statement below)
  • Create AuditTypes table with values populated (see statements below)
  • Each table to be audited needs the following columns:
    • Created [smalldatetime] – Default: Getdate()
    • CreatedByID [uniqueidentifier]
    • Modified [smalldatetime]
    • ModifiedByID [uniqueidentifier]
  • Create sp_build_triggers by running script. There are two things to attend to at the top of the stored procedure.  The first one is listing all the computed columns (they will not be audited).  This is done by inserting the table and column name for each into the table variable.  The second is defining the tables for which you do not want audit triggers created, including the audit tables.
  • Note: If the table name has been changed between stored procedure executions, the original triggers will either need to be renamed or deleted. The script does not automatically detect them if they are not named “tr_[Table Name]_[U/D]”.

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.

Happy coding!

sp_buid_triggers – Audit Tables

sp_buid_triggers

Tech Talk Live Blog Comment Guidelines:

One of our main goals at Tech Talk Live is to build a community. It is our hope that this blog can be a forum for discussion around our content. We see commenting as an integral part of this community. It allows everyone to participate, contribute, connect, and share relevant personal experience that adds value to the conversation. Respect counts. We believe you can disagree without being disagreeable. Please refrain from personal attacks, name calling, libel/defamation, hate speech, discriminatory or obscene/profane language, etc. Comments should keep to the topic at hand, and not be promotional or commercial in nature. Please do not link to personal blog posts, websites, or social media accounts that are irrelevant to the conversation. This is considered self-promotion. We welcome links that help further the conversation and reserve the right to delete those we deem unnecessary. The appearance of external links on this site does not constitute official endorsement on behalf of Tech Talk Live or Lancaster-Lebanon Intermediate Unit 13. You are solely responsible for the content that you post – please use your best judgment. We reserve the right to remove posts that do not follow these guidelines.

Leave a Reply

Your email address will not be published. Required fields are marked *

CONTACT

Tech Talk Live is the only conference of its kind in the region specifically designed for IT pros in education.


techtalklive@iu13.org
1020 New Holland Avenue, Lancaster, PA 17601

(717) 606-1770