Automagic Auditing

May 20, 2000

Automagic Auditing

Almost every database has some tables which need auditing where every change made to the data needs to be tracked. The changes, in addition to the change date and the user will be saved to another table.

I have developed a technique and a stored procedure which will do this automatically. Just call the stored procedure with your table name and auditing is immediately enabled.

The stored procedure will create a new table with the same columns as the table you are auditing with additional columns for the change date, and change type: Insert, Update, or Delete. If you change or add any columns to the table, you will first have to modify the audit table.

A trigger will be added to your table to capture changes into the audit table. Because the beginning columns of the audit table mirror your table, the insert can be done with a SELECT * which simplifies the code. This is one of the few coding techniques I have seen that should ever use a SELECT * owing to the potential for additional or removed columns to cause unintended effects.

The stored procedure is made to be compatible with both SQL Server 6.5 and 7.0. It won't replace triggers in either version unless you explicitly request drops via a parameter. The same holds for the audit table.

You may want to remake the auditing table several times as your development proceeds. The procedure can do this for you, but you will lose the existing auditing data.

Another feature is the ability to put the audit tables in a separate database. I have found this very useful to keep a primary database from growing to an unwieldy size. The default is a database called '?_Audit' where '?' is the name of the database containing the source table. Change the @DBName parameter to your preference.

Many extensions are possible to this basic schema. Simply by adding another column you could grab host_name(), USER_NAME(), SUSER_NAME(), APP_NAME(), etc. You might also want to restrict data capture via a where clause or IF UPDATE(colName) restriction.

The procedure installs in master so it can be used in any database and it follows my naming convention for master stored procedures which is to use the prefix: 'sp__'.

Call the proc from the database which contains the table you want to monitor and auditing will be immediately enabled.

Click here to view the code.

Check out for more SQL Server tools.

The Network for Technology Professionals



Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers