MS SQL Trigger for Audit

1) Using 'if UPDATE(Column)'
Column can not be a variable. This is painful for generic coding.

2) Using 'COLUMNS_UPDATED()'


BEGIN
--PRINT '@@rowcount = ' + cast(@@rowcount as varchar(10))
if @@rowcount=0 RETURN

DECLARE @UpdateDate varchar(21), @UserName varchar(128), @Type char(1)

-- Action
if exists (SELECT * FROM inserted)
if exists (SELECT * FROM deleted)
SELECT @Type = 'U'
else
SELECT @Type = 'I'
else
SELECT @Type = 'D'
--PRINT '@Type = ' + @Type

SELECT @UserName = system_user,
@UpdateDate = convert(varchar(8), getdate(), 112) + ' ' + convert(varchar(12), getdate(), 114)

--PRINT '@UserName = ' + @UserName + ', @UpdateDate='+@UpdateDate

--Get Tablename of the Trigger
DECLARE @TriggerID int, @TableID int, @TableName varchar(100)

SET @TriggerID = @@PROCID;
SELECT @TableID = parent_obj FROM sysobjects where xtype ='TR' and id = @TriggerID
SELECT @TableName = OBJECT_NAME(@TableID)
--PRINT @TableName

DECLARE @PKCols varchar(1000), @PKSELECT varchar(1000)
DECLARE @SQLString nvarchar(2000)

--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
--PRINT '@PKCols = '+@PKCols

-- Get primary key SELECT for insert
SELECT @PKSELECT = coalesce(@PKSELECT+'+','') + '''<' + COLUMN_NAME + '=''+convert(varchar(100),coalesce(i.' + COLUMN_NAME +',d.' + 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
--PRINT '@PKSELECT = '+@PKSELECT

if @PKCols is null
begin
raiserror('no PK on table %s', 16, -1, @TableName)
return
end

SELECT * into #ins FROM inserted
SELECT * into #del FROM deleted

declare @bit int, @field int, @maxfield int, @char int, @fieldname varchar(128)

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 or @Type in ('I','D')
begin
SELECT @fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION = @field
SELECT @SQLString = 'insert Audit (Type, TableName, PK, FieldName, OldValue, NewValue, UpdateDate, UserName)'
SELECT @SQLString = @SQLString + ' SELECT ''' + @Type + ''''
SELECT @SQLString = @SQLString + ',''' + @TableName + ''''
SELECT @SQLString = @SQLString + ',' + @PKSELECT
SELECT @SQLString = @SQLString + ',''' + @fieldname + ''''
SELECT @SQLString = @SQLString + ',convert(varchar(1000),d.' + @fieldname + ')'
SELECT @SQLString = @SQLString + ',convert(varchar(1000),i.' + @fieldname + ')'
SELECT @SQLString = @SQLString + ',''' + @UpdateDate + ''''
SELECT @SQLString = @SQLString + ',''' + @UserName + ''''
SELECT @SQLString = @SQLString + ' from #ins i full outer join #del d'
SELECT @SQLString = @SQLString + @PKCols
SELECT @SQLString = @SQLString + ' where i.' + @fieldname + ' <> d.' + @fieldname
SELECT @SQLString = @SQLString + ' or (i.' + @fieldname + ' is null and d.' + @fieldname + ' is not null)'
SELECT @SQLString = @SQLString + ' or (i.' + @fieldname + ' is not null and d.' + @fieldname + ' is null)' --PRINT '@SQLString='+@SQLString

--PRINT '@SQLString = ' + @SQLString
exec (@SQLString)
end
end


END


http://www.nigelrivett.net/AuditTrailTrigger.html
Generic audit trail trigger
Author Nigel Rivett

/*
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 (Type char(1), TableName varchar(128), PK varchar(1000), 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) ,
@Type char(1) ,
@PKSelect varchar(1000)

select @TableName = 'trigtest'

-- date and user
select @UserName = system_user ,
@UpdateDate = convert(varchar(8), getdate(), 112) + ' ' + convert(varchar(12), getdate(), 114)

-- Action
if exists (select * from inserted)
if exists (select * from deleted)
select @Type = 'U'
else
select @Type = 'I'
else
select @Type = 'D'

-- 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

-- Get primary key select for insert
select @PKSelect = coalesce(@PKSelect+'+','') + '''<' + COLUMN_NAME + '=''+convert(varchar(100),coalesce(i.' + COLUMN_NAME +',d.' + 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 or @Type in ('I','D')
begin
select @fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION = @field
select @sql = 'insert Audit (Type, TableName, PK, FieldName, OldValue, NewValue, UpdateDate, UserName)'
select @sql = @sql + ' select ''' + @Type + ''''
select @sql = @sql + ',''' + @TableName + ''''
select @sql = @sql + ',' + @PKSelect
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'
select * from Audit
select * from trigtest
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
delete trigtest
select * from Audit
select * from trigtest

go
drop table Audit
go
drop table trigtest
go



http://articles.techrepublic.com.com/5100-10878_11-6175865.html

CREATE TRIGGER tr_SalesHistory ON SalesHistory

FOR UPDATE

AS

BEGIN
DECLARE @FldsUpdated XML
DECLARE @ColumnsUpdated VARBINARY(100)


SET @ColumnsUpdated = COLUMNS_UPDATED()

SET @FldsUpdated =
(
SELECT COLUMN_NAME AS Name
FROM TRS.INFORMATION_SCHEMA.COLUMNS Field
WHERE
TABLE_NAME = 'SalesHistory' AND
sys.fn_IsBitSetInBitmask
(
@ColumnsUpdated,
COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME, 'ColumnID')
) <> 0
FOR XML AUTO, ROOT('Fields')
)


INSERT INTO SalesHistoryAudit
(SaleID, Product, SaleDate, SalePrice, ColumnsUpdated)
SELECT SaleID, Product, SaleDate, SalePrice, @FldsUpdated
FROM INSERTED

END

GO