Audit Trails - what a pain they can be. We all know that a dynamic audit trigger, that investigates the metadata and builds an audit insert statement on the fly, is easy to implement, but performs as well as my teenager gets up in the morning. A fixed audit trail trigger, hard-coded specifically for the table, minimizes the database workload, but the only task worse than building it is maintaining it.
Wouldn’t it be cool to run a procedure that examined the table’s metadata and used code-gen to build a perfect fixed audit trail trigger? When the table’s schema changes, simply rerun the procedure. I’ve been meaning to write this for about a year. I wanted to include it in the SQL Server 2005 Bible, but just didn’t have the time.
Four events coalesced for me two weeks ago regarding audit trail triggers. First, I saw a blog entry stating the only way to build a dynamic audit trail trigger was to use CLR. UGHGG. 2) A new client has an inconsistent audit trail system built into some stored procedures, but not all. They need an easy way to quickly build a trigger based audit trail. 3) A reader wrote asking for more information and advice on implementing the T-SQL dynamic audit trail trigger I wrote about in SQL Server 2000 Bible. I wanted to give him a better solution than my old dynamic trigger. And 4) I’m starting to edit and prep the SQL Server Bible for the next edition and on the list of new chapters was this idea for a better audit trail method.
So, here’s a script that creates the audit table and builds 4 stored procedures.
AutoAudit 'schema', 'table'
which:
1) creates dbo.audit table if not already there
2) adds created and modifed columns to table
3) builds insert, update, and modified audit trail trigger
AutoAditDrop 'schema', 'table'
which:
1) drops insert, update, and modified audit trail trigger
AutoAuditAll
AuditDropAll
1) runs the autoaudit or autoauditdrop for every table in the database other than the audit table.
This is only a version 1, so test before you put it in production. I’m open to suggestions and requests.
This version is limited to tables with single column primary keys. But that’s easy to change if enough readers ask for it. Also, in the next version I’m going to add error handling.