AuditSQL

create procedure spGenerateAuditTrailCode
@vcTable varchar(255) = null,
@iAuditBitmask Integer = 0,
@iUpdateBitmask Integer = 0
as
begin
If @iAuditBitmask = 0 or @iUpdateBitmask = 0 or @vcTable is null
begin
print ' '
print 'Error!'
print ' '
print 'USE:'
print 'spGenerateAuditTrailCode <table name>, <audit bitmask>, <update bitmask>'
print ' '
print 'Audit Bitmask Update Bitmask'
print '------------- --------------'
print '1 = create trigger For logging updates 1 = log old values'
print '2 = create trigger For logging inserts 2 = log new values'
print '4 = create trigger For logging deletes'
print ' '
print 'An update bitmask is passed only If a update trigger is requested.'
print ' '
print 'Note: code generated is not ready To run until the , is removed from the last'
print ' of Each column names.'
print ' '
return
end
/* Audit Bitmask
** 1 = record updates
** 2 = record inserts
** 4 = record deletes
**
** Update Bitmask
** 1 = log old values
** 2 = log new values
*/
Set nocount on
/*
** Create the audit table
*/
print '-- Create table --'
print ' '
print 'create table ' + @vcTable + 'Audit( '
print ' iAuditID Integer identity(1,1), '
print ' dAuditDate datetime = getdate(), '
print ' vcAuditAction char(1), '
print ' vcAuditType char(1), '
print ' vcAuditHostName varchar(15), '
print ' vcAuditProgramName varchar(15), '
print ' vcAuditNTUser varchar(15), '
print ' vcAuditLogin varchar(15), '

Select ' ' + sc.name + ' ' + st.name + Case
when sc.xtype In (175,99,231,167) Then
'(' + convert(varchar(4),sc.length) + ')' Else ''

End +
', '

from sysobjects so,
syscolumns sc,
systypes st
where so.type = 'U' and
so.name = @vcTable and
so.id = sc.id and
sc.xtype = st.xtype and
sc.xtype Not In (173,34,99,231,35,189,165)
print ')'
/*
** Create the update trigger
*/
If @iAuditBitmask & 1 = 1
begin
print ' '
print ' '
print ' '
print '-- Create update trigger --'
print ' '
print 'create trigger tu' + @vcTable + 'Audit '
print 'on ' + @vcTable
print 'for update'
print 'as'
print 'begin'
print ' '
print 'set nocount on'
/*
** Save the old value
*/
If @iUpdateBitmask & 1 = 1
begin
print ' '
print '/*'
print '** Store old value'
print '*/'
print ' '
print 'insert ' + @vcTable + 'Audit('
print 'dAuditDate, '
print 'vcAuditAction, '
print 'vcAuditType, '
print 'vcAuditHostName, '
print 'vcAuditProgramName, '
print 'vcAuditNTUser, '
print 'vcAuditLogin, '
Select '' + sc.name + ','
from sysobjects so,
syscolumns sc
where so.type = 'U' and
so.name = @vcTable and
so.id = sc.id and
sc.xtype Not In (173,34,99,231,35,189,165)
print ')'
print 'select getdate(),'
print '''U'','
print '''O'','
print 'left(sp.hostname,15),'
print 'left(sp.program_name,15),'
print 'left(sp.nt_username,15),'
print 'left(sp.loginame,15),'
Select 'd.' + sc.name + ','
from sysobjects so,
syscolumns sc
where so.type = 'U' and
so.name = @vcTable and
so.id = sc.id and
sc.xtype Not In (173,34,99,231,35,189,165)
print 'from deleted d,'
print ' master..sysprocesses sp'
print 'where sp.spid = @@spid'
End
/*
** Save the new value
*/
If @iUpdateBitmask & 1 = 1
begin
print ' '
print '/*'
print '** Store new value'
print '*/'
print ' '
print 'insert ' + @vcTable + 'Audit('
print 'dAuditDate, '
print 'vcAuditAction, '
print 'vcAuditType, '
print 'vcAuditHostName, '
print 'vcAuditProgramName, '
print 'vcAuditNTUser, '
print 'vcAuditLogin, '
Select '' + sc.name + ','
from sysobjects so,
syscolumns sc
where so.type = 'U' and
so.name = @vcTable and
so.id = sc.id and
sc.xtype Not In (173,34,99,231,35,189,165)
print ')'
print 'select getdate(),'
print '''U'','
print '''N'','
print 'left(sp.hostname,15),'
print 'left(sp.program_name,15),'
print 'left(sp.nt_username,15),'
print 'left(sp.loginame,15),'
Select 'i.' + sc.name + ','
from sysobjects so,
syscolumns sc
where so.type = 'U' and
so.name = @vcTable and
so.id = sc.id and
sc.xtype Not In (173,34,99,231,35,189,165)
print 'from inserted i,'
print ' master..sysprocesses sp'
print 'where sp.spid = @@spid'
End
print ' '
print 'set nocount off'
print 'end'
print 'go'
end
/*
** Create the insert trigger
*/
If @iAuditBitmask & 2 = 2
begin
print ' '
print ' '
print ' '
print '-- Create insert trigger --'
print ' '
print 'create trigger ti' + @vcTable + 'Audit '
print 'on ' + @vcTable
print 'for insert'
print 'as'
print 'begin'
print ' '
print 'set nocount on'
/*
** Save the new value
*/
print ' '
print '/*'
print '** Store new value'
print '*/'
print ' '
print 'insert ' + @vcTable + 'Audit('
print 'dAuditDate, '
print 'vcAuditAction, '
print 'vcAuditType, '
print 'vcAuditHostName, '
print 'vcAuditProgramName, '
print 'vcAuditNTUser, '
print 'vcAuditLogin, '
Select '' + sc.name + ','
from sysobjects so,
syscolumns sc
where so.type = 'U' and
so.name = @vcTable and
so.id = sc.id and
sc.xtype Not In (173,34,99,231,35,189,165)
print ')'
print 'select getdate(),'
print '''I'','
print '''N'','
print 'left(sp.hostname,15),'
print 'left(sp.program_name,15),'
print 'left(sp.nt_username,15),'
print 'left(sp.loginame,15),'
Select 'i.' + sc.name + ','
from sysobjects so,
syscolumns sc
where so.type = 'U' and
so.name = @vcTable and
so.id = sc.id and
sc.xtype Not In (173,34,99,231,35,189,165)
print 'from inserted i,'
print ' master..sysprocesses sp'
print 'where sp.spid = @@spid'
print ' '
print 'set nocount off'
print 'end'
print 'go'
end
/*
** Create the delete trigger
*/
If @iAuditBitmask & 4 = 4
begin
print ' '
print ' '
print ' '
print '-- Create delete trigger --'
print ' '
print 'create trigger td' + @vcTable + 'Audit '
print 'on ' + @vcTable
print 'for delete'
print 'as'
print 'begin'
print ' '
print 'set nocount on'
/*
** Save the deleted value
*/
print ' '
print '/*'
print '** Store deleted value'
print '*/'
print ' '
print 'insert ' + @vcTable + 'Audit('
print 'dAuditDate, '
print 'vcAuditAction, '
print 'vcAuditType, '
print 'vcAuditHostName, '
print 'vcAuditProgramName, '
print 'vcAuditNTUser, '
print 'vcAuditLogin, '
Select '' + sc.name + ','
from sysobjects so,
syscolumns sc
where so.type = 'U' and
so.name = @vcTable and
so.id = sc.id and
sc.xtype Not In (173,34,99,231,35,189,165)
print ')'
print 'select getdate(),'
print '''D'','
print '''O'','
print 'left(sp.hostname,15),'
print 'left(sp.program_name,15),'
print 'left(sp.nt_username,15),'
print 'left(sp.loginame,15),'
Select 'd.' + sc.name + ','
from sysobjects so,
syscolumns sc
where so.type = 'U' and
so.name = @vcTable and
so.id = sc.id and
sc.xtype Not In (173,34,99,231,35,189,165)
print 'from deleted d,'
print ' master..sysprocesses sp'
print 'where sp.spid = @@spid'
print ' '
print 'set nocount off'
print 'end'
print 'go'
end
Set nocount off
end
1) the audit log table,
2) sample code for the insert, update, and delete
triggers,
3) by default, the code logs old and new values
during and update, deleted records and newly
inserted records.

Note:
the code DOES require minor editing to remove
the last "," from each list of fields. It tracks
the AuditDate, Hostname, Program name, NT User name,
SQL Login in addition to all of the columns from the
source table.

This code has saved us hundreds of hours of tedious
manual trigger writing! Grab it! If you don't need it
now, you WILL someday. :)

By: kerlin

Inputs:
tablename

Returns:
Cut and paste code (with minoredits) to generate the
audit table along with

Assumes:
Works on SQL 7.0. Not tested on SQL 6.5.
Run it without any parameters to get an explanation
of how to use it.