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