Audit Trail Generator for Microsoft SQL

Cedric Baelemans

Rate me:

4.94/5 (55 votes)

27 Oct 20078 min read

Discussion of audit trail techniques and a script to automate audit trail implementation on a Microsoft SQL database

Download source - 1.9 KB

Screenshot - AuditTrailGenerator_ManagementStudio.jpg

Introduction

There are different reasons for having an audit trail on your data. Some companies have to do it because of legal obligations, such as Sarbanes-Oxley or FDA regulations. But I also find it very useful for debugging purposes. It shows you what was in your database at any point in time.

In this article I will explain the method I prefer for implementing an audit trail. Next, I will introduce a script to automate the implementation of the audit trail in the database.

Audit Trail With Shadow Table and Triggers

So how to implement an audit trail? Different visions exist. The one I prefer is to use a shadow table for each table that exists in the database. Every time a record is inserted, updated or deleted in a table, the record is also inserted in the corresponding shadow table. For inserting the record in the shadow table too, I use triggers on the original table that will fire whenever something happens. Other names you may encounter on the web for shadow tables are archive table or audit table.

Let's make this clear with a small example.

Screenshot - AuditTrailGenerator_diagram.gif

On the left side, you see the structure of a table called Users containing three columns. I refer to this table as the base table. On the right, you see the shadow table for this table. The shadow table contains all columns from the Users table, plus some extra columns:

AuditId: This the primary key of the shadow table. It is an identity field.
AuditAction: This is a one letter code to indicate the kind of operation. Values are I, U or D, for insert, update and delete respectively.
AuditDate: The date and time when the action occurred. The default value is set to getdate(), an SQL function that returns the current date and time.
AuditUser: The user who performed the action. The default value is set to suser_sname(), an SQL function that returns the user name of the user currently connected.
AuditApp: The application that was used. The default value is set to (('App=('+rtrim(isnull(app_name(),'')))+') '). This allows you to tell which application was used to modify the data, e.g. App=(Microsoft SQL Server Management Studio Express).

To fill up the shadow table, I define triggers on the Users table. We need three triggers: one for inserts, one for updates, and one for deletes. The code for the insert action is shown below. Those for updates and deletes are similar. If you are new to triggers, see Brief about Triggers in SQL Server 2000 by SquaredRomi.

SQL

CREATE TRIGGER tr_users_Insert ON dbo.users
FOR INSERT AS INSERT INTO users_shadow(UserId,FirstName,LastName,AuditAction)
SELECT UserId,FirstName,LastName,'I' FROM Inserted

The columns that are filled up by the trigger are only the data columns from the base table (userid, FirstName and LastName) and the AuditAction column. All other columns in the shadow table (AuditId, AuditDate, AuditUser and AuditApp) are filled up by their default value definition.

So what are the strengths and weaknesses of this approach? Let's start with the strengths:

It completely separates the current data from the audit trail. The old values are no longer in the base table but in the shadow table. There are no soft deletes, where deleted records are flagged as being deleted instead of being actually deleted.
It can easily be implemented on existing databases. If originally you did not foresee audit trailing, you can add it afterwards. The only thing you need to do is add the triggers on the base tables and create the shadow table. No changes have to be made to stored procedures or applications working with your database.
It always triggers. E.g. if you connect to your database through Enterprise Manager and you modify the data by hand, the triggers fire and the shadow table is updated accordingly.

The method also has some drawbacks:

The entire record is copied to the shadow table, including the columns that were not changed. In our example, if you change the firstname of a user in the base table, the lastname is also copied to the shadow table although it did not change. Hence, the shadow table will take up more space than strictly needed.
A trigger cannot be used on all column data types. Text, Ntext, and Image are not supported. The reason is that they are not stored in the record itself. The record only holds a pointer to the data. In SQL 2005, the timestamp is not supported either.
The number of tables doubles, although I personally don't find this an objection.
The audit trail is on a table level instead of on an action level. If during a single save operation in your application multiple tables in your database get updated, there is no link between the different transactions that took place on the different tables. The only thing that links them together is that they occurred at (almost) the same moment and by the same user.

The Audit Trail Generator Script

If you have 50 tables in your database, adding an audit trail using the method just described means adding another 50 tables and creating 150 triggers. This is why I have created the audit trail generator. It saves time and avoids typo errors. See the link on top of this article to download the code.

The audit trail generator is written as a stored procedure. Hence, you don't need any other tools. RichardRoe used codesmith as a generator, but this means you have to buy this tool first.

The stored procedure takes four arguments:

@TableName: The name of the table to which you want to add an audit trail, e.g. users
@Owner: The owner of the table. The default value is dbo
@AuditNameExtention: The extension you want for the shadow table name. E.g., if you set it to _shadow, the audit table for users will be called users_shadow. The default value is _shadow
@DropAuditTable: A bit to specify if the shadow table can be dropped. If 1, the existing audit table will be dropped and recreated. Of course, you lose all data in there. This is especially useful when you are still in development, but you may want to do this on a production system. The default value is 0.

The stored procedure will discover the columns in the original table by querying the system tables of SQL Server. These system tables are used by SQL Server itself to store the structure of the tables. The query to get all info about the table is shown below. For a complete overview of the system tables in SQL Server, see the Microsoft MSDN site.

SQL

SELECT b.name, c.name as TypeName, b.length, _
    b.isnullable, b.collation, b.xprec, b.xscale
FROM sysobjects a
inner join syscolumns b on a.id = b.id
inner join systypes c on b.xtype = c.xtype and c.name <> 'sysname'
WHERE a.id = object_id(N'[' + @Owner + '].[' + @TableName + ']')
and OBJECTPROPERTY(a.id, N'IsUserTable') = 1
ORDER BY b.colId

The image below shows the results if we launch this query for our Users table.

Screenshot - AuditTrailGenerator_QueryResult.gif

The remainder of the stored procedure loops over the result of this query using a cursor, and dynamically builds up the SQL statements in a string to create the shadow table and to add the triggers to the original table. These statements are then executed with the EXEC command. I will not go into the details of it, since it is straight forward.

Using the Script

The script is a stored procedure, so using it means calling the stored procedure. In its simplest form, you only need to set the @TableName parameter because for all other parameters, default values have been specified. The following statement can be launched from a query window.

SQL

EXECUTE GenerateAudittrail 'Users'

The following example shows what it looks like if all parameter values are specified.

SQL

EXECUTE GenerateAudittrail 'Users', 'dbo','_shadow', 0

The script is very handy to quickly create a shadow table for a given database table. However, it was not designed to modify a shadow table to reflect changes to the corresponding base table. In this case, it can only drop the shadow table, losing all records in it, and recreate it. Set the @DropAuditTable to 1 to force dropping and recreating the shadow table.

There are two articles I would like to mention that relate to this subject.

Nigel Rivett wrote an article describing an audit trail mechanism similar to the one described here, but where he tries to tackle some of the drawbacks I described. He uses one central audit trail table for all tables in the database, instead of a separate shadow table for each original table. Of course, it avoids the many shadow tables but he has to opt for varchars to store all values, regardless of what type they are in the original tables. But the most interesting point he makes is the logic that he builds into the triggers. The old values and the new values are compared and only the changed columns are stored in the audit trail. This can save storage space as the entire record is not duplicated. There is only one caveat. The time needed to modify data in a table increases since the trigger, which fires as a result of this modification, will take longer to complete since it contains much more logic compared to the simple triggers created by the audit trail generator presented here.

Ben Allfree criticizes the audit trail approach I took here in Record Versioning with SQL Server and proposes an alternative. Unfortunately, he does not mention triggers and moves audit trail logic into the code that accesses his database, such as stored procedures. Hence, it is not possible to add an audit trail to an existing database with the method he proposes, without changing the applications accessing that database. Another thing I dislike is the soft delete approach he takes, flagging old versions of records as inactive instead of actually deleting them. By moving older versions of records to a separate shadow table, I kept the original table clean and the number of records low, without impacting the speed of select statements.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Written By

Cedric Baelemans

Belgium

My name is Cedric and I live near Brussels, Belgium. Although I have an educational background in IT, I don't program for a living. My career has been more as an IT project leader and manager. But I want to stay up to date with the evolutions. Therefore, I dive into code after hours once in a while. Just to keep up with the programmers at work. You can read my blog at http://blog.baelemans.com

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Tagged as

Stats