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:
- 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.
- The table must have a primary key. Ideally, these values should be unchanging, or updates will be erroneously logged as separate insert/delete events.
- 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.
- 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.)
- 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).