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