February 12, 2007 at 12:53 am
I created a trigger that logs updates made to a table (audit trail) and it is working for most of the tables I have except those that have text, ntext and image data type fields. On tables with those type fields, i made the trigger an instead of trigger and thought of just forcing the update to the base table in the instead of trigger. Below is the code of my trigger but I am getting the error "INSTEAD OF TRIGGERS do not support direct recursion. Trigger execution failed." Can anyone tell me how to make the action that was suppose to happen (if the trigger I had was an after trigger) happen in an instead of trigger? views are not an option says the dba. Any help would be appreciated! Thanks!
--------------------------------------------------------------------------------------
CREATE trigger complaints_auditlog_trg on dbo.complaints instead of 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) ,
@StaffKey int ,
@Type char(1) ,
@PKSelect varchar(1000)
--debug code
--print 'passed var declaration'
--specify table name here
select @TableName = 'complaints'
-- date and user
select @StaffKey = 111 ,
@UpdateDate = convert(varchar(8), getdate(), 112) + ' ' + convert(varchar(12), getdate(), 114)
--debug code
--print 'beginning action code'
-- Action
if exists (select * from inserted)
if exists (select * from deleted)
select @Type = 'U'
else
select @Type = 'I'
else
select @Type = 'D'
--debug code
--print @Type
-- 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
--debug code
--print @PKCols
-- Get primary key select for insert
select @PKSelect = 'coalesce(i.' + COLUMN_NAME +',d.' + 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
--debug code
--print @PKSelect
if @PKCols is null
begin
raiserror('no PK on table %s', 16, -1, @TableName)
return
end
declare @SetStmt varchar(1000)
select @SetStmt = ''
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 si_audit_log (table_name, column_name, primary_key, Original_Value, new_value, date_modified, staff_key) '
select @sql = @sql + 'select ''' + @TableName + ''''
select @sql = @sql + ',''' + @fieldname + ''''
select @sql = @sql + ',' + @PKSelect
--select @sql = @sql + ',convert(varchar(4000),d.' + @fieldname + ')'
--select @sql = @sql + ',coalesce(convert(varchar(4000),i.' + @fieldname + '),' + '''Deleted''' + ')'
--the above 2 lines were replaced by the next few lines
select @sql = @sql + ',coalesce(convert(varchar(4000),d.' + @fieldname + '),' + '''*NULL*''' + ')'
if @Type = 'D'
select @sql = @sql + ',' + '''*Deleted*'''
else
select @sql = @sql + ',coalesce(convert(varchar(4000),i.' + @fieldname + '),' + '''*NULL*''' + ')'
select @sql = @sql + ',''' + @UpdateDate + ''''
select @sql = @sql + ',' + convert(varchar(4000), @StaffKey)
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)'
--debug code
--print @sql
exec (@sql)
if @Type = 'U'
select @SetStmt = @SetStmt + ',' + @fieldname + ' = #ins.' + @fieldname
end
end
if @Type = 'U'
begin
--debug code
--select * from #ins
--select * from #del
--print @SetStmt
declare @UpdateStmt varchar(1000)
select @UpdateStmt = 'update complaints set ' + substring(@SetStmt, 2, len(@SetStmt)) + ' from #ins join complaints on complaints.complaint_key = #ins.complaint_key'
--debug code
print @UpdateStmt
exec (@UpdateStmt)
end
else
-- @Type = 'D'
begin
declare @DeleteStmt varchar(1000)
select @DeleteStmt = 'delete from complaints where complaint_key in (select complaint_key from #del)'
--debug code
print @DeleteStmt
exec (@DeleteStmt)
end
February 13, 2007 at 1:37 am
From your debug statements it looks like you should have seen this:
if exists (select * from inserted)
BEGIN
if exists (select * from deleted)
select @Type = 'U'
else
select @Type = 'I'
END
else
select @Type = 'D'
When in doubt, indent to check.
Andy
February 13, 2007 at 2:51 am
- Keep in mind that what you do within a trigger is "in transaction" ! Timeouts, locking,... !!
- keep in mind there may be more than one row involved in the action so you should join inserted and deleted on the PK to determine U,I,D
- It a litle bit more work for the dba to sync then needed update at columnlevel and your trigger-text will be longer, but are you willing to pay the runtime-price when you want to build all this dynamic ??
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply