This is my code to create auditing against a table. Copy this into a query window, change the name of the table at the top, and run. You’ll get a printout of the code that you need (no objects are created at this point)
Review the code, past it into a query window, and run. This will create a table and three triggers (INSERT, UPDATE, DELETE) on the target table. Any change to the target table will be written to the audit table.
If the target table is ‘SOP10100’, your audit table will be named ‘SOP10100AUDIT’
declare @tablename varchar(50)
declare @output varchar(8000)
declare @fieldName varchar(500)
declare @fieldType varchar(50)
set @tablename = 'SOP10100'
set @output = ''
-------------------------
-- CREATE THE AUDIT TABLE
-------------------------
-- drop the table if it exists
print 'IF EXISTS( SELECT NAME'
print ' FROM sysobjects'
print ' WHERE name = N' + char(39) + @tablename + 'AUDIT' + char(39)
print ' AND type = ' + char(39) + 'U' + char(39) + ')'
print ' DROP TABLE ' + @tablename + 'AUDIT' + char(13)
print 'GO'
-- drop the trigger if it exists
print 'IF EXISTS( SELECT NAME' + char(13)
print ' FROM sysobjects'
print ' WHERE name = N' + char(39) + 't_4P_' + @tablename + 'AUDIT_INS' + char(39)
print ' AND type = ' + char(39) + 'TR' + char(39) + ')'
print ' DROP TRIGGER ' + 't_4P_' + @tablename + 'AUDIT_INS'
print 'GO'
-- drop the trigger if it exists
print 'IF EXISTS( SELECT NAME'
print ' FROM sysobjects'
print ' WHERE name = N' + char(39) + 't_4P_' + @tablename + 'AUDIT_DEL' + char(39)
print ' AND type = ' + char(39) + 'TR' + char(39) + ')'
print ' DROP TRIGGER ' + 't_4P_' + @tablename + 'AUDIT_DEL'
print 'GO'
-- drop the trigger if it exists
print 'IF EXISTS( SELECT NAME'
print ' FROM sysobjects'
print ' WHERE name = N' + char(39) + 't_4P_' + @tablename + 'AUDIT_UPD' + char(39)
print ' AND type = ' + char(39) + 'TR' + char(39) + ')'
print ' DROP TRIGGER ' + 't_4P_' + @tablename + 'AUDIT_UPD'
print 'GO'
declare curFields CURSOR for
select syscolumns.name,
case when xusertype = 56 then 'int'
when xusertype = 48 then 'tinyint'
when xusertype = 36 then 'uniqueidentifier'
when xusertype = 52 then 'smallint'
when xusertype = 60 then 'money'
when xusertype = 104 then 'bit'
when xusertype = 35 then 'text'
when xusertype = 61 then 'datetime'
when xusertype = 189 then 'timestamp'
when xusertype = 173 then 'binary(' + convert(varchar(4),length) + ')'
when xusertype = 175 then 'char(' + convert(varchar(4),length) + ')'
when xusertype = 167 then 'varchar(' + convert(varchar(4),length) + ')'
when xusertype = 108 then 'numeric(' + convert(varchar(4),xprec) + ',' +
convert(varchar(4),xscale) + ')'
end as fieldType
from sysobjects
left join syscolumns on syscolumns.id = sysobjects.id
where sysobjects.name = @tablename
set @output = @output + 'CREATE TABLE ' + @tablename + 'AUDIT (' + char(13)
set @output = @output + ' ' + 'dtCreated datetime, ' + char(13)
set @output = @output + ' ' + 'vchrLineType varchar(50), ' + char(13)
OPEN curFields
FETCH NEXT FROM curFields
INTO @fieldName, @fieldType
WHILE @@FETCH_STATUS = 0
BEGIN
if @fieldType <> 'text'
begin
set @output = @output + ' ' + @fieldName + ' ' + @fieldType + ', ' + char(13)
end
FETCH NEXT FROM curFields
INTO @fieldName, @fieldType
END
CLOSE curFields
DEALLOCATE curFields
set @output = left(@output, len(@output) - 3)
set @output = @output + ')' + char(13) + char(13) + 'GO' + char(13) + char(13)
print @output
set @output = ''
----------------------------
-- CREATE THE INSERT TRIGGER
----------------------------
declare curFields CURSOR for
select syscolumns.name,
case when xusertype = 56 then 'int'
when xusertype = 48 then 'tinyint'
when xusertype = 36 then 'uniqueidentifier'
when xusertype = 52 then 'smallint'
when xusertype = 60 then 'money'
when xusertype = 104 then 'bit'
when xusertype = 35 then 'text'
when xusertype = 61 then 'datetime'
when xusertype = 189 then 'timestamp'
when xusertype = 173 then 'binary(' + convert(varchar(4),length) + ')'
when xusertype = 175 then 'char(' + convert(varchar(4),length) + ')'
when xusertype = 167 then 'varchar(' + convert(varchar(4),length) + ')'
when xusertype = 108 then 'numeric(' + convert(varchar(4),xprec) + ',' +
convert(varchar(4),xscale) + ')'
end as fieldType
from sysobjects
left join syscolumns on syscolumns.id = sysobjects.id
where sysobjects.name = @tablename
and colstat <> 1
set @output = @output + 'CREATE TRIGGER t_4P_' + @tablename + 'AUDIT_INS' + char(13)
set @output = @output + 'ON ' + @tablename + char(13)
set @output = @output + 'FOR INSERT' + char(13)
set @output = @output + 'AS' + char(13)
set @output = @output + 'BEGIN' + char(13)
set @output = @output + 'INSERT INTO ' + @tablename + 'AUDIT ('
declare @vchrFieldList as varchar(8000)
set @vchrFieldList = ''
OPEN curFields
FETCH NEXT FROM curFields
INTO @fieldName, @fieldType
WHILE @@FETCH_STATUS = 0
BEGIN
if @fieldType <> 'text'
begin
set @vchrFieldList = @vchrFieldList + @fieldName + ', '
end
FETCH NEXT FROM curFields
INTO @fieldName, @fieldType
END
CLOSE curFields
DEALLOCATE curFields
set @vchrFieldList = left(@vchrFieldList, len(@vchrFieldList) - 1)
set @output = @output + @vchrFieldList + ', dtCreated, vchrLineType)' + char(13)
set @output = @output + ' SELECT ' + @vchrFieldList + ', ' + 'getdate()' + ', ' + char(39) + 'insert' + char(39) + char(13)
set @output = @output + ' FROM inserted' + char(13)
set @output = @output + 'END' + char(13) + char(13)
set @output = @output + 'GO' + char(13) + char(13)
print @output
set @output = ''
-----------------------------
-- CREATE THE DELETED TRIGGER
-----------------------------
declare curFields CURSOR for
select syscolumns.name,
case when xusertype = 56 then 'int'
when xusertype = 48 then 'tinyint'
when xusertype = 36 then 'uniqueidentifier'
when xusertype = 52 then 'smallint'
when xusertype = 60 then 'money'
when xusertype = 104 then 'bit'
when xusertype = 35 then 'text'
when xusertype = 61 then 'datetime'
when xusertype = 189 then 'timestamp'
when xusertype = 173 then 'binary(' + convert(varchar(4),length) + ')'
when xusertype = 175 then 'char(' + convert(varchar(4),length) + ')'
when xusertype = 167 then 'varchar(' + convert(varchar(4),length) + ')'
when xusertype = 108 then 'numeric(' + convert(varchar(4),xprec) + ',' +
convert(varchar(4),xscale) + ')'
end as fieldType
from sysobjects
left join syscolumns on syscolumns.id = sysobjects.id
where sysobjects.name = @tablename
and colstat <> 1
set @output = @output + 'CREATE TRIGGER t_4P_' + @tablename + 'AUDIT_DEL' + char(13)
set @output = @output + 'ON ' + @tablename + char(13)
set @output = @output + 'FOR DELETE' + char(13)
set @output = @output + 'AS' + char(13)
set @output = @output + 'BEGIN' + char(13)
set @output = @output + 'INSERT INTO ' + @tablename + 'AUDIT ('
set @vchrFieldList = ''
OPEN curFields
FETCH NEXT FROM curFields
INTO @fieldName, @fieldType
WHILE @@FETCH_STATUS = 0
BEGIN
if @fieldType <> 'text'
begin
set @vchrFieldList = @vchrFieldList + @fieldName + ', '
end
FETCH NEXT FROM curFields
INTO @fieldName, @fieldType
END
CLOSE curFields
DEALLOCATE curFields
set @vchrFieldList = left(@vchrFieldList, len(@vchrFieldList) - 1)
set @output = @output + @vchrFieldList + ', dtCreated, vchrLineType)' + char(13)
set @output = @output + ' SELECT ' + @vchrFieldList + ', ' + 'getdate()' + ', ' + char(39) + 'delete' + char(39) + char(13)
set @output = @output + ' FROM deleted' + char(13)
set @output = @output + 'END' + char(13) + char(13)
set @output = @output + 'GO' + char(13) + char(13)
print @output
set @output = ''
----------------------------
-- CREATE THE UPDATE TRIGGER
----------------------------
declare curFields CURSOR for
select syscolumns.name,
case when xusertype = 56 then 'int'
when xusertype = 48 then 'tinyint'
when xusertype = 36 then 'uniqueidentifier'
when xusertype = 52 then 'smallint'
when xusertype = 60 then 'money'
when xusertype = 104 then 'bit'
when xusertype = 35 then 'text'
when xusertype = 61 then 'datetime'
when xusertype = 189 then 'timestamp'
when xusertype = 173 then 'binary(' + convert(varchar(4),length) + ')'
when xusertype = 175 then 'char(' + convert(varchar(4),length) + ')'
when xusertype = 167 then 'varchar(' + convert(varchar(4),length) + ')'
when xusertype = 108 then 'numeric(' + convert(varchar(4),xprec) + ',' +
convert(varchar(4),xscale) + ')'
end as fieldType
from sysobjects
left join syscolumns on syscolumns.id = sysobjects.id
where sysobjects.name = @tablename
and colstat <> 1
set @output = @output + 'CREATE TRIGGER t_4P_' + @tablename + 'AUDIT_UPD' + char(13)
set @output = @output + 'ON ' + @tablename + char(13)
set @output = @output + 'FOR UPDATE' + char(13)
set @output = @output + 'AS' + char(13)
set @output = @output + 'BEGIN' + char(13)
set @output = @output + 'INSERT INTO ' + @tablename + 'AUDIT ('
set @vchrFieldList = ''
OPEN curFields
FETCH NEXT FROM curFields
INTO @fieldName, @fieldType
WHILE @@FETCH_STATUS = 0
BEGIN
if @fieldType <> 'text'
begin
set @vchrFieldList = @vchrFieldList + @fieldName + ', '
end
FETCH NEXT FROM curFields
INTO @fieldName, @fieldType
END
CLOSE curFields
DEALLOCATE curFields
set @vchrFieldList = left(@vchrFieldList, len(@vchrFieldList) - 1)
set @output = @output + @vchrFieldList + ', dtCreated, vchrLineType)' + char(13)
set @output = @output + ' SELECT ' + @vchrFieldList + ', ' + 'getdate()' + ', ' + char(39) + 'update del' + char(39) + char(13)
set @output = @output + ' FROM deleted' + char(13) + char(13)
set @output = @output + 'INSERT INTO ' + @tablename + 'AUDIT ('
set @output = @output + @vchrFieldList + ', dtCreated, vchrLineType)' + char(13)
set @output = @output + ' SELECT ' + @vchrFieldList + ', ' + 'getdate()' + ', ' + char(39) + 'update ins' + char(39) + char(13)
set @output = @output + ' FROM inserted' + char(13)
set @output = @output + 'END' + char(13) + char(13)
set @output = @output + 'GO' + char(13) + char(13)
print @output