CodePlex is long gone, but the script can now be found here: https://github.com/koenmd/AutoAudit/blob/master/README.md

A few years ago I discovered a great utility called “Auto Audit” on CodePlex by Paul Nielson (author of “Sql Bible”) and John Sigouin. The best thing is that it creates triggers that audit insert, update, and deletes on any desired table. It creates a global audit table, and creates triggers specific to each of your tables.  It can optionally add these columns to each table: CreatedDate, CreatedBy, ModifiedDate, ModifiedBy, and RowVersion (incrementing INT).  It includes views to see your audits, and it does add eight of its own tables to your database (all nicely put in the ‘Audit’ schema).  It also logs who is doing DDL commands, so you know, for example, which DBA or which developer, added some column to some schema.

It’s like a mini-framework to make sure all your triggers and auditing are done in a consistent manner, and it writes all the tedious trigger code for you. This of course allows you to see who changed what and when. The other benefit is that it standardizes the process, and builds the trigger for you. I’ve been in other companies where we had similar functionality, but it was all hand-coded, and rather tedious to create and update the triggers.

When you download it, what you get is just one big .sql file, 6422 lines long.  You run that script in each database in which you want to use Auto Audit.

Installing

I had a database called NealDemo with one table dbo.Employee (in red box below).  Running the Auto Audit script added 8 of it’s own tables, but as of yet, it does not touch the Employee table.  We have to do the next step for that to happen.

Running the above will also create some stored procedures, views, and most importantly SchemaAuditDDLTrigger DDL Trigger.  This will allow you to see who for instance modified a table structure, because that will get audited too.

 Turning On Audit for a Table

So to turn on auditing for one single table, you do this (overriding the schema or other parms as desired):

exec [Audit].[pAutoAudit] @TableName=’Employee’, @BaseTableDDL=1

Note: If you don’t specify @BaseTableDDL=1, the 5 columns won’t get added to the table.  Below is an example of my employee table, and it’s fairly obvious which 5 columns were added.

If you are ready to turn it on everywhere, on all tables, instead use pAutoAuditAll.

It created three triggers on my Employee table.  The update trigger alone is 305 lines of code, and the other two are about 150 lines each.

Viewing the Audits

I ran the following update:

Rather than creating one audit table for each table, Auto Audit uses one consolidated audit table:

 

However, it does create a view for each table.  In addition it creates a view called Employee_Deleted to show any rows deleted from my Employee table..

Now look what happens when you “Alter” a table.  There is a Database Trigger called “SchemaAuditDDLTrigger”. It catches the changes and logs them, as well as rebuilds the triggers on the Employee table.

Table Function

There’s also a table function that can be used on a single key.  Below I show the difference in the vAuditAll and using the function against that same key:

 

Auditing DDL Changes

Now look what happens when you “Alter” a table.  There is a Database Trigger called “SchemaAuditDDLTrigger”. It catches the changes and logs them, as well as rebuilds the triggers on the Employee table.

 

To view who changed the table or anything in the schema using DDL, check the Audit.SchemaAudit table:

Removing AutoAudit from a Table

exec [Audit].[pAutoAuditDrop] 
  @SchemaName = 'dbo',
  @TableName = 'Employee', 
           --0 = don't drop Base Table DDL Columns, 1 = drop  Base Table DDL Columns
  @DropBaseTableDDLColumns = 0,   
           --0 = don't drop audit triggers on tables, 1 = drop audit triggers on tables
        @DropBaseTableTriggers = 1, 
  @DropBaseTableViews =1

 

Summary

I’m so impressed with this utility.  If you have no auditing at all on your database, you can turn this on in just a matter of minutes.  Note however, that it could impact your performance, so be sure to benchmark if you are pushing the boundaries on performance already.

 

 
Alternate keywords for search: AutoEdit AutoUpdate Auto-Audit Auto-Edit Auto-Update Samples