Here is a good storeproc to audit your database tables for changes
first create table for your audit list
****** Object: Table [dbo].[Auditlist] Script Date: 07/12/2012 15:10:35 ******/ ANSI_NULLS ON QUOTED_IDENTIFIER ON ANSI_PADDING ON TABLE [dbo].[Auditlist]([sz30tablename] [varchar]
)(128) NOT NULL ON [PRIMARY]GO
SET
GO
ANSI_PADDING OFF
then insert into this the tables you want to audit
insert into auditlist values ('tablename')
then create the proc
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_addaudittable]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_addaudittable]
GO
USE [databasename]
GO
/****** Object: StoredProcedure [dbo].[sp_addaudittable] Script Date: 07/12/2012 14:46:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[sp_addaudittable] @tablename varchar(40)
as
declare @databasename varchar(30)
declare @status smallint
declare @statement varchar(8000)
declare @id int
declare @audittable char(50)
declare @identity tinyint
set nocount on
if not exists(select name from sysobjects where name = 'Auditlist' and type = 'U')
begin
print '--Auditlist table does not exist'
return 1
end
if not exists(select name from sysobjects where name = @tablename and type = 'U' and uid = 1)
begin
print '--Source table does not exist'
return 1
end
if not exists(select sz30tablename from dbo.auditlist where sz30tablename = @tablename)
insert into dbo.auditlist (sz30tablename) values (@tablename)
if exists(select name from sysobjects where name like @tablename + '_a' and type = 'U' and uid = 1)
begin
print '--Audit table already exists - please drop first'
return 1
end
if exists(select name from sysobjects where name like @tablename + '_a' and type = 'U' and uid <> 1)
begin
print '--Invalid audit table. Not dbo.'
return 1
end
SELECT @databasename = db_name(),@audittable = @tablename+'_a'
SELECT @status = (status & 4)
FROM master..sysdatabases
WHERE name = db_name()
if @status = 0
begin
exec sp_dboption @databasename, 'select into/bulkcopy',TRUE
end
if exists(
SELECT *
FROM syscolumns
WHERE (status & 128) = 128 and
object_name(id) = @tablename )
SET @identity = 1
else
SET @identity = 0
if @identity = 0
SET @statement = 'select * into ' + 'dbo.' + @tablename + '_a from ' + @tablename + ' where 1>2'
else
SET @statement = 'select t1.* into ' + 'dbo.' + @tablename + '_a from ' + @tablename + ' t1 join ' + @tablename + ' t2 on t1.identitycol = t2.identitycol where 1>2'
execute (@statement)
SELECT @statement = 'alter table dbo.'+ @tablename + '_a ' + 'add sz30auditusername varchar(30) null, sz1auditType char(1) null, sz30audithostname char(30) null, dtaudittimestamp datetime null'
execute (@statement)
-- Now we need to get rid of the timestamp column
declare @TimestampColname varchar(100)
SELECT @TimestampColname = syscolumns.name
FROM syscolumns
JOIN systypes on syscolumns.xusertype = systypes.xusertype
WHERE systypes.name like 'Timestamp' and id = object_id(@tablename)
if @@rowcount > 0
begin
SET @statement = 'alter table dbo.' + @tablename + '_a drop column ' + @TimestampColname
exec(@statement)
end
-- Now get a list of columns
declare @cols table (colid int, colname varchar(100))
INSERT
INTO @cols
SELECT colid,
syscolumns.name
FROM syscolumns
JOIN systypes on syscolumns.xusertype = systypes.xusertype
WHERE systypes.name not like 'timestamp' and id = object_id(@tablename)
declare @colname varchar(100)
declare @colnames varchar(8000)
declare @first tinyint
SET @colnames = ''
SET @first = 1
while (1>0)
begin
SELECT @colname = colname
FROM @cols
WHERE colid = (SELECT min(colid)
FROM @cols)
if @@rowcount = 0
begin
break
end
else
begin
if @first = 1
begin
SET @first = 0
end
else
begin
SET @colnames = @colnames + ','
end
end
SET @colnames = @colnames + '[' + @colname+']'
DELETE
FROM @cols
WHERE colname = @colname
end
declare @addcols varchar(1000)
SET @addcols = ',[sz30auditusername],[sz1auditType],[sz30audithostname],[dtaudittimestamp]'
select @id = deltrig from sysobjects where name = @tablename and type = 'U'
if @id = 0
begin
print '--Warning this table has no delete trigger - Printing please run'
SELECT @statement = 'Create trigger tr' + @tablename + 'Delete on ' + @tablename + ' for delete as if exists(select sz30tablename from auditlist where sz30tablename=''' + @tablename + ''') insert into ' + @tablename + '_a (' + @colnames+ @addcols + ') select ' + @colnames +',user_name(),''D'',host_name(),getdate() from deleted'
print @statement
print 'go'
end
else
begin
if not exists(select * from sysdepends where depid = object_id('auditlist') and id = @id)
print '--Warning this table''s delete trigger does not access audit log'
if not exists(select * from sysdepends where depid = object_id(@audittable) and id = @id)
print '--Warning this table''s delete trigger does not insert into audit log'
SELECT @statement = 'Create trigger tr' + @tablename + 'Delete on ' + @tablename + ' for delete as if exists(select sz30tablename from auditlist where sz30tablename=''' + @tablename + ''') insert into ' + @tablename + '_a (' + @colnames+ @addcols + ') select ' + @colnames +',user_name(),''D'',host_name(),getdate() from deleted'
print @statement
print 'go'
end
select @id = Updtrig from sysobjects where name = @tablename and type = 'U'
if @id = 0
begin
print '--Warning this table has no update trigger - Printing please run'
SELECT @statement = 'Create trigger tr' + @tablename + 'Update on ' + @tablename + ' for update as if exists(select sz30tablename from auditlist where sz30tablename=''' + @tablename + ''') insert into ' + @tablename + '_a (' + @colnames+ @addcols + ') select ' + @colnames +',user_name(),''U'',host_name(),getdate() from inserted'
print @statement
print 'go'
end
else
begin
if not exists(select * from sysdepends where depid = object_id('auditlist') and id = @id)
print '--Warning this table''s update trigger does access audit log'
if not exists(select * from sysdepends where depid = object_id(@audittable) and id = @id)
print '--Warning this table''s update trigger does not insert into audit log'
SELECT @statement = 'Create trigger tr' + @tablename + 'Update on ' + @tablename + ' for update as if exists(select sz30tablename from auditlist where sz30tablename=''' + @tablename + ''') insert into ' + @tablename + '_a (' + @colnames+ @addcols + ') select ' + @colnames +',user_name(),''U'',host_name(),getdate() from inserted'
print @statement
print 'go'
end
select @id = Instrig from sysobjects where name = @tablename and type = 'U'
if @id = 0
begin
print '--Warning this table has no insert trigger - Printing please run'
SELECT @statement = 'Create trigger tr' + @tablename + 'Insert on ' + @tablename + ' for insert as if exists(select sz30tablename from auditlist where sz30tablename=''' + @tablename + ''') insert into ' + @tablename + '_a (' + @colnames+ @addcols + ') select ' + @colnames +',user_name(),''I'',host_name(),getdate() from inserted'
print @statement
print 'go'
end
else
begin
if not exists(select * from sysdepends where depid = object_id('auditlist') and id = @id)
print '--Warning this table''s insert trigger does access audit log'
if not exists(select * from sysdepends where depid = object_id(@audittable) and id = @id)
print '--Warning this table''s insert trigger does not insert into audit log'
SELECT @statement = 'Create trigger tr' + @tablename + 'Insert on ' + @tablename + ' for insert as if exists(select sz30tablename from auditlist where sz30tablename=''' + @tablename + ''') insert into ' + @tablename + '_a (' + @colnames+ @addcols + ') select ' + @colnames +',user_name(),''I'',host_name(),getdate() from inserted'
print @statement
print 'go'
end
if @status = 0
begin
exec sp_dboption @databasename, 'select into/bulkcopy',FALSE
end
GO
then run proc
exec sp_addaudittable 'tablename'
This will create a table tablename_a
it will then print the code to create the triggers, copy this out and run it into the database you now have the triggers for audit.
SET
GO
SET
GO
SET
GO
CREATE
first create table for your audit list
****** Object: Table [dbo].[Auditlist] Script Date: 07/12/2012 15:10:35 ******/ ANSI_NULLS ON QUOTED_IDENTIFIER ON ANSI_PADDING ON TABLE [dbo].[Auditlist]([sz30tablename] [varchar]
)(128) NOT NULL ON [PRIMARY]GO
SET
GO
ANSI_PADDING OFF
then insert into this the tables you want to audit
insert into auditlist values ('tablename')
then create the proc
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_addaudittable]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_addaudittable]
GO
USE [databasename]
GO
/****** Object: StoredProcedure [dbo].[sp_addaudittable] Script Date: 07/12/2012 14:46:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[sp_addaudittable] @tablename varchar(40)
as
declare @databasename varchar(30)
declare @status smallint
declare @statement varchar(8000)
declare @id int
declare @audittable char(50)
declare @identity tinyint
set nocount on
if not exists(select name from sysobjects where name = 'Auditlist' and type = 'U')
begin
print '--Auditlist table does not exist'
return 1
end
if not exists(select name from sysobjects where name = @tablename and type = 'U' and uid = 1)
begin
print '--Source table does not exist'
return 1
end
if not exists(select sz30tablename from dbo.auditlist where sz30tablename = @tablename)
insert into dbo.auditlist (sz30tablename) values (@tablename)
if exists(select name from sysobjects where name like @tablename + '_a' and type = 'U' and uid = 1)
begin
print '--Audit table already exists - please drop first'
return 1
end
if exists(select name from sysobjects where name like @tablename + '_a' and type = 'U' and uid <> 1)
begin
print '--Invalid audit table. Not dbo.'
return 1
end
SELECT @databasename = db_name(),@audittable = @tablename+'_a'
SELECT @status = (status & 4)
FROM master..sysdatabases
WHERE name = db_name()
if @status = 0
begin
exec sp_dboption @databasename, 'select into/bulkcopy',TRUE
end
if exists(
SELECT *
FROM syscolumns
WHERE (status & 128) = 128 and
object_name(id) = @tablename )
SET @identity = 1
else
SET @identity = 0
if @identity = 0
SET @statement = 'select * into ' + 'dbo.' + @tablename + '_a from ' + @tablename + ' where 1>2'
else
SET @statement = 'select t1.* into ' + 'dbo.' + @tablename + '_a from ' + @tablename + ' t1 join ' + @tablename + ' t2 on t1.identitycol = t2.identitycol where 1>2'
execute (@statement)
SELECT @statement = 'alter table dbo.'+ @tablename + '_a ' + 'add sz30auditusername varchar(30) null, sz1auditType char(1) null, sz30audithostname char(30) null, dtaudittimestamp datetime null'
execute (@statement)
-- Now we need to get rid of the timestamp column
declare @TimestampColname varchar(100)
SELECT @TimestampColname = syscolumns.name
FROM syscolumns
JOIN systypes on syscolumns.xusertype = systypes.xusertype
WHERE systypes.name like 'Timestamp' and id = object_id(@tablename)
if @@rowcount > 0
begin
SET @statement = 'alter table dbo.' + @tablename + '_a drop column ' + @TimestampColname
exec(@statement)
end
-- Now get a list of columns
declare @cols table (colid int, colname varchar(100))
INSERT
INTO @cols
SELECT colid,
syscolumns.name
FROM syscolumns
JOIN systypes on syscolumns.xusertype = systypes.xusertype
WHERE systypes.name not like 'timestamp' and id = object_id(@tablename)
declare @colname varchar(100)
declare @colnames varchar(8000)
declare @first tinyint
SET @colnames = ''
SET @first = 1
while (1>0)
begin
SELECT @colname = colname
FROM @cols
WHERE colid = (SELECT min(colid)
FROM @cols)
if @@rowcount = 0
begin
break
end
else
begin
if @first = 1
begin
SET @first = 0
end
else
begin
SET @colnames = @colnames + ','
end
end
SET @colnames = @colnames + '[' + @colname+']'
DELETE
FROM @cols
WHERE colname = @colname
end
declare @addcols varchar(1000)
SET @addcols = ',[sz30auditusername],[sz1auditType],[sz30audithostname],[dtaudittimestamp]'
select @id = deltrig from sysobjects where name = @tablename and type = 'U'
if @id = 0
begin
print '--Warning this table has no delete trigger - Printing please run'
SELECT @statement = 'Create trigger tr' + @tablename + 'Delete on ' + @tablename + ' for delete as if exists(select sz30tablename from auditlist where sz30tablename=''' + @tablename + ''') insert into ' + @tablename + '_a (' + @colnames+ @addcols + ') select ' + @colnames +',user_name(),''D'',host_name(),getdate() from deleted'
print @statement
print 'go'
end
else
begin
if not exists(select * from sysdepends where depid = object_id('auditlist') and id = @id)
print '--Warning this table''s delete trigger does not access audit log'
if not exists(select * from sysdepends where depid = object_id(@audittable) and id = @id)
print '--Warning this table''s delete trigger does not insert into audit log'
SELECT @statement = 'Create trigger tr' + @tablename + 'Delete on ' + @tablename + ' for delete as if exists(select sz30tablename from auditlist where sz30tablename=''' + @tablename + ''') insert into ' + @tablename + '_a (' + @colnames+ @addcols + ') select ' + @colnames +',user_name(),''D'',host_name(),getdate() from deleted'
print @statement
print 'go'
end
select @id = Updtrig from sysobjects where name = @tablename and type = 'U'
if @id = 0
begin
print '--Warning this table has no update trigger - Printing please run'
SELECT @statement = 'Create trigger tr' + @tablename + 'Update on ' + @tablename + ' for update as if exists(select sz30tablename from auditlist where sz30tablename=''' + @tablename + ''') insert into ' + @tablename + '_a (' + @colnames+ @addcols + ') select ' + @colnames +',user_name(),''U'',host_name(),getdate() from inserted'
print @statement
print 'go'
end
else
begin
if not exists(select * from sysdepends where depid = object_id('auditlist') and id = @id)
print '--Warning this table''s update trigger does access audit log'
if not exists(select * from sysdepends where depid = object_id(@audittable) and id = @id)
print '--Warning this table''s update trigger does not insert into audit log'
SELECT @statement = 'Create trigger tr' + @tablename + 'Update on ' + @tablename + ' for update as if exists(select sz30tablename from auditlist where sz30tablename=''' + @tablename + ''') insert into ' + @tablename + '_a (' + @colnames+ @addcols + ') select ' + @colnames +',user_name(),''U'',host_name(),getdate() from inserted'
print @statement
print 'go'
end
select @id = Instrig from sysobjects where name = @tablename and type = 'U'
if @id = 0
begin
print '--Warning this table has no insert trigger - Printing please run'
SELECT @statement = 'Create trigger tr' + @tablename + 'Insert on ' + @tablename + ' for insert as if exists(select sz30tablename from auditlist where sz30tablename=''' + @tablename + ''') insert into ' + @tablename + '_a (' + @colnames+ @addcols + ') select ' + @colnames +',user_name(),''I'',host_name(),getdate() from inserted'
print @statement
print 'go'
end
else
begin
if not exists(select * from sysdepends where depid = object_id('auditlist') and id = @id)
print '--Warning this table''s insert trigger does access audit log'
if not exists(select * from sysdepends where depid = object_id(@audittable) and id = @id)
print '--Warning this table''s insert trigger does not insert into audit log'
SELECT @statement = 'Create trigger tr' + @tablename + 'Insert on ' + @tablename + ' for insert as if exists(select sz30tablename from auditlist where sz30tablename=''' + @tablename + ''') insert into ' + @tablename + '_a (' + @colnames+ @addcols + ') select ' + @colnames +',user_name(),''I'',host_name(),getdate() from inserted'
print @statement
print 'go'
end
if @status = 0
begin
exec sp_dboption @databasename, 'select into/bulkcopy',FALSE
end
GO
then run proc
exec sp_addaudittable 'tablename'
This will create a table tablename_a
it will then print the code to create the triggers, copy this out and run it into the database you now have the triggers for audit.
SET
GO
SET
GO
SET
GO
CREATE
