Creating the Audit Trigger

				
--**************************************
-- Name: Creating the Audit Trigger
-- Description:Create an audit trigger on the tables in order to record the changes to the data by the user
-- By: Kazi Khalid
--
-- Inputs:It will put out an error message if there is no primary key on the table
--
-- Assumes:You will need to change @TableName to match the table to be audit traile
--
--This code is copyrighted and has-- limited warranties.Please see http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=829&lngWId=5--for details.--**************************************

/*
This trigger audit trails all changes made to a table.
It will place in the table Audit all inserted, deleted, changed columns in the table on which it is placed.
It will put out an error message if there is no primary key on the table
You will need to change @TableName to match the table to be audit trailed
*/
--Set up the tables
if exists (select * from sysobjects where id = object_id(N'[dbo].[Audit]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Audit]
go
create table Audit (TableName varchar(128), FieldName varchar(128), OldValue varchar(1000), NewValue varchar(1000), UpdateDate datetime, UserName varchar(128))
go
if exists (select * from sysobjects where id = object_id(N'[dbo].[trigtest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[trigtest]
go
create table trigtest (i int not null, j int not null, s varchar(10), t varchar(10))
go
alter table trigtest add constraint pk primary key (i, j)
go
create trigger tr_trigtest on trigtest for insert, update, delete
as
declare @bit int ,
	@field int ,
	@maxfield int ,
	@char int ,
	@fieldname varchar(128) ,
	@TableName varchar(128) ,
	@PKCols varchar(1000) ,
	@sql varchar(2000), 
	@UpdateDate varchar(21) ,
	@UserName varchar(128)
	
	select @TableName = 'trigtest'
	-- date and user
	select 	@UserName = system_user ,
		@UpdateDate = convert(varchar(8), getdate(), 112) + ' ' + convert(varchar(12), getdate(), 114)
	-- get list of columns
	select * into #ins from inserted
	select * into #del from deleted
	
	-- Get primary key columns for full outer join
	select	@PKCols = coalesce(@PKCols + ' and', ' on') + ' i.' + c.COLUMN_NAME + ' = d.' + c.COLUMN_NAME
	from	INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
		INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
	where 	pk.TABLE_NAME = @TableName
	and	CONSTRAINT_TYPE = 'PRIMARY KEY'
	and	c.TABLE_NAME = pk.TABLE_NAME
	and	c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
	
	if @PKCols is null
	begin
		raiserror('no PK on table %s', 16, -1, @TableName)
		return
	end
	
	select @field = 0, @maxfield = max(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName
	while @field < @maxfield
	begin
		select @field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION > @field
		select @bit = (@field - 1 )% 8 + 1
		select @bit = power(2,@bit - 1)
		select @char = ((@field - 1) / 8) + 1
		if substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0
		begin
			select @fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION = @field
			select @sql = 		'insert Audit (TableName, FieldName, OldValue, NewValue, UpdateDate, UserName)'
			select @sql = @sql + 	' select ''' + @TableName + ''''
			select @sql = @sql + 	',''' + @fieldname + ''''
			select @sql = @sql + 	',convert(varchar(1000),d.' + @fieldname + ')'
			select @sql = @sql + 	',convert(varchar(1000),i.' + @fieldname + ')'
			select @sql = @sql + 	',''' + @UpdateDate + ''''
			select @sql = @sql + 	',''' + @UserName + ''''
			select @sql = @sql + 	' from #ins i full outer join #del d'
			select @sql = @sql + 	@PKCols
			select @sql = @sql + 	' where i.' + @fieldname + ' <> d.' + @fieldname 
			select @sql = @sql + 	' or (i.' + @fieldname + ' is null and d.' + @fieldname + ' is not null)' 
			select @sql = @sql + 	' or (i.' + @fieldname + ' is not null and d.' + @fieldname + ' is null)' 
			
			exec (@sql)
		end
	end
go
insert trigtest select 1,1,'hi', 'bye'
insert trigtest select 2,2,'hi', 'bye'
insert trigtest select 3,3,'hi', 'bye'
update trigtest set s = 'hibye' where i <> 1
update trigtest set s = 'bye' where i = 1
update trigtest set s = 'bye' where i = 1
update trigtest set t = 'hi' where i = 1
select * from Audit
select * from trigtest
go
drop table Audit
go
drop table trigtest
go