fuerteventura
asked on
Audit trigger not logging deletions
I Have created a trigger to log all activity on a table. It is logging all details perfectly for updates and additions, however it is not writing a row when you merely delete a row. My code is below - can anyone spot where I'm goiing wrong?
ALTER trigger *Tr_myTableName *on dbo.myTableName for insert, update,
delete as
declare @bit int ,
@field int ,
@maxfield int ,
@char int ,
@fieldname varchar(*700*) ,
@TableName varchar(*700*) ,
@PKCols varchar(*700*) ,
@sql varchar(*8000*),
@UpdateDate varchar(*100*) ,
@UserName varchar(*800*) ,
@Type char(*9*) ,
@PKSelect varchar(*6500*),
select @TableName = 'myTableName'
select @UserName = system_user ,
if exists (select * from inserted)
if exists (select * from deleted)
select @Type = '*UDT' *
else
select @Type = 'INS'
else
select @Type = 'DEL'
select * into #del from deleted
select * into #ins from inserted
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
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
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 ('INS','DEL')
begin
select @fieldname = COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = @TableName and
ORDINAL_POSITION = @field
select @sql = 'insert
*tblAudit (audType, TblNam, primaryKey, FldName,
Old, New, audDat,
User*)'
*select @sql = @sql + '
select @sql = @sql + ',''' +
@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
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER