Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted May 20, 2000

Automagic Auditing

By Aaron Goldman

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 www.sqldevpro.com for more SQL Server tools.

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM