Dave Britten

I find myself often needing to have some kind of audit trail for a table in SQL Server, i.e. some way to look at before-and-after data, who changed it, when, etc. I've set up enough of these manually that they were becoming formulaic, so I figured why not put together a procedure that creates them for me and writes the necessary trigger?

Attached is the version I'm currently using. The syntax is pretty simple. Here's an example, a real one that I just set up:

EXEC sp_CreateDataAudit
        'dbo',
        'IV00200',
        'audit',
        'IV00200',
        NULL,
        'tr_IV00200_Audit',
        'SERLNMBR,UNITCOST,RCTSEQNM,VNDRNMBR,SERLNSLD,BIN',
        1

Here's a detailed breakdown of the parameters:

CREATE PROCEDURE sp_CreateDataAudit
    @table_schema sysname = NULL, --Schema of the table to audit (optional but recommended)
    @table_name sysname, --Name of the table to audit
    @audit_table_schema sysname = NULL, --Schema in which to create the audit table (optional but recommended)
    @audit_table_name sysname, --Name of the audit table to create
    @audit_table_filegroup sysname = NULL, --Filegroup in which to create the audit table (optional)
    @audit_trigger_name sysname, --Name of the trigger to create on the table being audited
    @include_cols nvarchar(max), --Columns to include and watch for changes. Primary key columns are always included. (optional, all columns will be included if ommitted)
    @force_varchar_varbinary bit = 0 --Create char/nchar/binary columns as varchar/nvarchar/varbinary in the audit table

Some important notes before you use this:

1. This script will create a procedure named sp_CreateDataAudit in the master database, and mark it as a system object. This allows the procedure to be run from within any database. If you don't like this behavior, modify the script accordingly.
2. The table must have a primary key. Ideally, these values should be unchanging, or updates will be erroneously logged as separate insert/delete events.
3. The table can't have any .NET data types. I'm sure this procedure could be amended to handle such tables, I just haven't had the need. I also have not tested this with XML data types, but it may work.
4. The procedure will not set any permissions for the audit table. Make sure the appropriate users/roles have INSERT permission! (I do this by creating all the audit tables in an "audit" schema, where the public role has only INSERT permission.)
5. The audit table will be created in the same database as the table being audited. I prefer to keep the audit data transactionally consistent with the data being audited in the event of a restore, so I haven't added any cross-database capability.

The audit table will have this general format:

<Primary_Key_Columns> --All the primary key columns from the base table, with their original names
Old_<Column_Name> --Original values for all columns being audited (all NULL for INSERT events)
New_<Column_Name> --New values for all columns being audited (all NULL for DELETE events)
_Action char(1) --'I'nsert, 'U'pdate, 'D'elete
_Changed datetime --When the change occurred
_Changed_By nvarchar(128) --Who changed the data (SUSER_SNAME())
_Statement nvarchar(max) --SQL text courtesy of DBCC INPUTBUFFER
_Application nvarchar(128) --Program name (APP_NAME())

The audit table is also created with a (non-unique) clustered index on all the primary key columns plus _Changed, and a nonclustered index on just _Changed.

Disclaimer: This code comes without warranty of any kind. Translation: Do it in a test database first!

UPDATE: I've attached an updated version of the script (3/14/2014). I discovered that trying to access text or image columns in either the INSERTED or DELETED virtual tables within a trigger fails with an error. This updated version disallows auditing of text or image columns (it will automatically exclude them, and warn you if you attempted to include any).

Create-Data-Audit.sql