/*
Created 29th Jan 2007: PI
Explanation: Modification of Nigel's excellent script for audit triggers. Changed in various ways including use of environmental variables, using a direct comparison of columns in the updated trigger and the use of XML text for auditing.
This trigger will place all details in the table called "Audit".
The batch will produce an error message if there is no primary key on the source table.
You just will need to change the environmental variable to match the table to be audit for a singleton or use the parent script "AddAllTriggers.sql" to iterate through all your tables.
*/
--:setvar tablename Project

if exists (select * from sys.triggers where name = 'tr_Audit_Update_$(tablename)')
drop trigger tr_Audit_Update_$(tablename)
if exists (select * from sys.triggers where name = 'tr_Audit_Delete_$(tablename)')
drop trigger tr_Audit_Delete_$(tablename)
if exists (select * from sys.triggers where name = 'tr_Audit_Insert_$(tablename)')
drop trigger tr_Audit_Insert_$(tablename)
go

create trigger tr_Audit_Update_$(tablename) on $(tablename) for update
as

declare @field int ,
@maxfield int ,
@fieldname varchar(128) ,
@sql varchar(max),
@UpdateDate varchar(100) ,
@UserID int ,
@UserName varchar(200),
@PKColumn varchar(1000),
@ParmDefinition nvarchar(2000),
@fieldvalueINS_sql varchar(max),
@fieldvalueDEL_sql varchar(max),
@fieldvalueINS varchar(max),
@fieldvalueDEL varchar(max),
@count int,
@totalrowcount int,
@Audit_Details varchar(max),
@SQLString nvarchar(max)

-- date and user
set @UserID = isnull((select top (1) Last_Updated_By from inserted),'')
set @UserName = isnull((select isnull(forename,'') + ' ' + isnull(surname,'') from dbo.TESA_User where [User_ID] = @UserID),'N/A')
set @UpdateDate = convert(varchar(100), getdate(), 113)

-- get list of columns and add a final column for the recordcount
select *, 99999 as RecordCount into #ins from inserted
select * into #del from deleted

-- now I need to populate the recordcount column to use as a reference rowcount
-- can't use identity column as there will already be one

set @totalrowcount = (select count(*) from #ins)
set @count = 1
while @count <= @totalrowcount
begin
update top (1) #ins set RecordCount = @count where RecordCount > @count
set @count = @count + 1
end

-- Get primary key column to be able to stipulate the join
select @PKColumn = 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 @PKColumn is null
begin
raiserror('no PK on table $(tablename)', 16, -1)
return
end

select @maxfield = max(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = '$(tablename)'

-- iterate through the rows here
declare @loopcount int
declare @character_maximum_length int
set @loopcount = 1
while @loopcount <= @totalrowcount
begin
select @field = 0
set @sql = ''
set @Audit_Details = '<audit>' + char(10) + char(13)
set @Audit_Details = @Audit_Details + '<table TableName="$(tablename)" ChangeDate="' + @UpdateDate + '" UserName="' + @UserName +'">'

-- iterate through the fields here
while @field < @maxfield
begin
select @field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = '$(tablename)' and ORDINAL_POSITION > @field
select @fieldname = '[' + COLUMN_NAME + ']', @character_maximum_length = isnull(character_maximum_length,0) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = '$(tablename)' and ORDINAL_POSITION = @field
set @SQLString = 'Select top 1 '
set @SQLString = @SQLString + '@fieldvalueINS_sql = cast(i.' + @fieldname + ' as varchar(max)), '
set @SQLString = @SQLString + '@fieldvalueDEL_sql = cast(d.' + @fieldname + ' as varchar(max)) '
set @SQLString = @SQLString + 'from #ins i inner join #del d on i.' + @PKColumn + ' = d.' + @PKColumn
set @SQLString = @SQLString + ' where RecordCount = ' + cast(@loopcount as varchar(100))
set @ParmDefinition = N'@fieldvalueINS_sql varchar(max) output,'
set @ParmDefinition = @ParmDefinition + N'@fieldvalueDEL_sql varchar(max) output';

exec sp_executesql @SQLString, @ParmDefinition,@fieldvalueINS_sql = @fieldvalueINS OUTPUT, @fieldvalueDEL_sql = @fieldvalueDEL OUTPUT; --, @fieldvaluePK_sql = @fieldvaluePK OUTPUT;
if (isnull(@fieldvalueINS,'xxx123') <> isnull(@fieldvalueDEL,'xxx123')) or ('[' + @PKColumn + ']' = @fieldname) -- values are different or we have the PK column
begin
set @Audit_Details = @Audit_Details + '<column ColumnName="' + @fieldname + '" InsertedValue="' + isnull(replace(@fieldvalueINS,'''',''''''),'[NULL]') + '" DeletedValue="' + isnull(replace(@fieldvalueDEL,'''',''''''),'[NULL]') + '"/> ' + char(10) + char(13)
end

end

set @Audit_Details = @Audit_Details + '</table>' + char(10) + char(13)
set @Audit_Details = @Audit_Details + '</audit>'

-- make the audit insert for this row
select @sql = 'insert Audit (Audit_Operation, Audit_TableName, Audit_Details, UpdateDate, UserName)'
select @sql = @sql + ' select ''Update'''
select @sql = @sql + ',''$(tablename)'''
select @sql = @sql + ',''' + @Audit_Details + ''''
select @sql = @sql + ',''' + @UpdateDate + ''''
select @sql = @sql + ',''' + @UserName + ''''
exec (@sql)

set @loopcount = @loopcount + 1
end
go

create trigger tr_Audit_Insert_$(tablename) on $(tablename) for Insert
as
declare @bit int ,
@field int ,
@maxfield int ,
@char int ,
@fieldname varchar(128) ,
@sql varchar(max),
@InsertDate varchar(100) ,
@UserID int,
@UserName varchar(200),
@IntVariable int,
@SQLString nvarchar(max),
@ParmDefinition nvarchar(500),
@fieldname_sql varchar(1000),
@fieldvalue varchar(max),
@Audit_Details varchar(max),
@RowCounter int,
@totalInserted int,
@character_maximum_length int

set @Audit_Details = ''
set @RowCounter = 1
set @totalInserted = (select count(*) from inserted)

-- date and user
set @UserID = isnull((select top (1) Created_By from inserted),'')
set @UserName = isnull((select isnull(forename,'') + ' ' + isnull(surname,'') from dbo.TESA_User where [User_ID] = @UserID),'N/A')
set @InsertDate = convert(varchar(100), getdate(), 113)

-- get list of rows. We'll remove these one by one as they are audited
select * into #ins from inserted
select @maxfield = max(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = '$(tablename)'

-- iterate through the inserted rows. Normally this'll be one row only, but we can't guarantee this
while @RowCounter <= @totalInserted
begin
select @field = 0
set @sql = ''
set @Audit_Details = '<audit>' + char(10) + char(13)
set @Audit_Details = @Audit_Details + '<table TableName="$(tablename)" ChangeDate="' + @InsertDate + '" UserName="' + @UserName +'">'
--select @Audit_Details
-- iterate through the fields
while @field < @maxfield
begin
select @field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = '$(tablename)' and ORDINAL_POSITION > @field
select @fieldname = '[' + COLUMN_NAME + ']', @character_maximum_length = isnull(character_maximum_length,0) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = '$(tablename)' and ORDINAL_POSITION = @field
if @character_maximum_length <> -1
begin
set @SQLString = 'Select top 1 @fieldvalue_sql = cast(' + @fieldname + ' as varchar(max)) from #ins i'
set @ParmDefinition = '@fieldvalue_sql varchar(max) output';
exec sp_executesql @SQLString, @ParmDefinition,@fieldvalue_sql = @fieldvalue OUTPUT;
set @Audit_Details = @Audit_Details + '<column ColumnName="' + @fieldname + '" InsertedValue="' + isnull(replace(@fieldvalue,'''',''''''),'[NULL]') + '"/> ' + char(10) + char(13)
end
end

set @Audit_Details = @Audit_Details + '</table>' + char(10) + char(13)
set @Audit_Details = @Audit_Details + '</audit>'

-- make the audit insert for this row
select @sql = 'insert Audit (Audit_Operation, Audit_TableName, Audit_Details, UpdateDate, UserName)'
select @sql = @sql + ' select ''Insert'''
select @sql = @sql + ',''$(tablename)'''
select @sql = @sql + ',''' + @Audit_Details + ''''
select @sql = @sql + ',''' + @InsertDate + ''''
select @sql = @sql + ',''' + @UserName + ''''

exec (@sql)

--remove the one we just added
delete top(1) from #ins
set @RowCounter = @RowCounter + 1
end
go

create trigger tr_Audit_Delete_$(tablename) on $(tablename) for Delete
as
declare @bit int ,
@field int ,
@maxfield int ,
@char int ,
@fieldname varchar(128) ,
@sql varchar(max),
@DeleteDate varchar(100) ,
@UserID int,
@UserName varchar(200),
@IntVariable int,
@SQLString nvarchar(max),
@ParmDefinition nvarchar(500),
@fieldname_sql varchar(1000),
@fieldvalue varchar(max),
@Audit_Details varchar(max),
@RowCounter int,
@totalDeleted int,
@character_maximum_length int

set @Audit_Details = ''
set @RowCounter = 1
set @totalDeleted = (select count(*) from Deleted)
-- date and user
set @UserID = isnull((select top (1) Last_Updated_By from Deleted),'')
set @UserName = isnull((select isnull(forename,'') + ' ' + isnull(surname,'') from dbo.TESA_User where [User_ID] = @UserID),'N/A')
set @DeleteDate = convert(varchar(100), getdate(), 113)
-- get list of rows. We'll remove these one by one as they are audited
select * into #del from Deleted
select @maxfield = max(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = '$(tablename)'
-- iterate through the inserted rows. Normally this'll be one row only, but we can't guarantee this
while @RowCounter <= @totalDeleted
begin
select @field = 0
set @sql = ''
set @Audit_Details = '<audit>' + char(10) + char(13)
set @Audit_Details = @Audit_Details + '<table TableName="$(tablename)" ChangeDate="' + @DeleteDate + '" UserName="' + @UserName +'">'

-- iterate through the fields
while @field < @maxfield
begin
select @field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = '$(tablename)' and ORDINAL_POSITION > @field
select @fieldname = '[' + COLUMN_NAME + ']', @character_maximum_length = isnull(character_maximum_length,0) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = '$(tablename)' and ORDINAL_POSITION = @field
if @character_maximum_length <> -1
begin
set @SQLString = 'Select top 1 @fieldvalue_sql = cast(' + @fieldname + ' as varchar(max)) from #del d'
set @ParmDefinition = '@fieldvalue_sql varchar(max) output';
exec sp_executesql @SQLString, @ParmDefinition, @fieldvalue_sql = @fieldvalue OUTPUT;
set @Audit_Details = @Audit_Details + '<column ColumnName="' + @fieldname + '" DeletedValue="' + isnull(replace(@fieldvalue,'''',''''''),'[NULL]') + '"/> ' + char(10) + char(13)
end
end

set @Audit_Details = @Audit_Details + '</table>' + char(10) + char(13)
set @Audit_Details = @Audit_Details + '</audit>'

-- make the audit insert for this row
select @sql = 'insert Audit (Audit_Operation, Audit_TableName, Audit_Details, UpdateDate, UserName)'
select @sql = @sql + ' select ''Delete'''
select @sql = @sql + ',''$(tablename)'''
select @sql = @sql + ',''' + @Audit_Details + ''''
select @sql = @sql + ',''' + @DeleteDate + ''''
select @sql = @sql + ',''' + @UserName + ''''

exec (@sql)

--remove the one we just added
delete top(1) from #del
set @RowCounter = @RowCounter + 1
end
go