February 5, 2009 at 7:19 pm
I am using the below trigger to updated an audti tabel in my database. The code works great, except for the fact it cannot handle image fields. I am new to this trigger & have dug through trying to figure out a way to say "if field type is image thn skip".
Any sugesstions...I am truly at a loss on this.
Thanks!
/*
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
ALTER trigger [dbo].[tr_Audit_LIST_TABLE] on [dbo].[LIST_TABLE] for insert, update, delete
as
declare @bit int ,
@field int ,
@maxfield int ,
@char int ,
@fieldname varchar(128) ,
@TableName varchar(255) ,
@PKCols varchar(1000) ,
@sql varchar(2000),
@UpdateDate varchar(21) ,
@UserName varchar(128) ,
@Type VARCHAR(20) ,
@PKSelect varchar(1000)
select @TableName = 'LIST_TABLE'
-- 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 = 'Update'
else
select @Type = 'Insert'
else
select @Type = 'Delete'
-- 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
fromINFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = @TableName
andCONSTRAINT_TYPE = 'PRIMARY KEY'
andc.TABLE_NAME = pk.TABLE_NAME
andc.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
-- Get primary key select for insert
select @PKSelect = coalesce(@PKSelect+'+','') + ''' '''
fromINFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = @TableName
andCONSTRAINT_TYPE = 'PRIMARY KEY'
andc.TABLE_NAME = pk.TABLE_NAME
andc.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 ('Insert','Delete')
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 + ' 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
February 6, 2009 at 6:52 am
You need to check the data_type column in information_schema.columns for IMAGE or varbinary and when varbinary then if the character_maximum_length = -1.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 6, 2009 at 8:32 am
Hi
Do you have audit on Image field. I am curious to know what is the reson behind to this.
Thanks
Vijaya Kadiyala
February 6, 2009 at 12:25 pm
Thanks for the info. I figured that I needed to check for the data_type, but where the code needs to be placed I guess is the next question? Do I need to use an IF stmt in the surrent trigger?
Thanks!
February 7, 2009 at 9:26 am
I don't know if changing the structure of this trigger is a consideration... but you really don't want to use loops and dynamic sql in triggers. It will likely perform really badly. Using dynamic sql to GENERATE triggers, especially audit triggers where you want to just track changes on every field for a range of tables... that's perfectly fine.
It shouldn't actually be all that hard to convert what you have now. You'd use this script to create your trigger rather than having it AS your trigger. Then, you'd have a properly made set based trigger that didn't use loops or dynamic sql. The only downside to this approach would be that it would not automatically update the trigger for new fields. You'd have to rerun the alter trigger script whenever you added fields to the tables that you wanted to track.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply