Trigger - works most of the time but fail when one field is updated

[jzelhart]

I have a trigger - works for all other tables at this point. It works for Insert, Update of multiple fields but fails 95% of the time when updating 1 field. I can duplicate this in SQL Server Management Studio manually. i have tried rebuilding statistic and indexes on the table as well as the system databases. The only difference in the triggers are the name of the trigger, name of the table and a variable for the name of the table. If I edit multiple fields or add fields to the table I get different results but still inconsistent. Anybody have any ideas where I should look to resolve this? I thought I might have to export the data, drop the table, create a new table and import the data back. Not really convienent in the long run...

Thanks in advance

Jennifer

USE [DatabaseName]
GO
/****** Object:  Trigger [dbo].[tblLookupCounties_TRG]    Script Date: 02/15/2008 13:03:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Description:	Audit Trail Trigger
-- =============================================
ALTER TRIGGER [dbo].[tblLookupCounties_TRG]
   ON  [dbo].[tblLookupCounties_T]
   FOR INSERT,DELETE,UPDATE
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)


BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	select @TableName = 'tblLookupCounties_T'

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

	-- Action
	if exists (select * from inserted)
		begin
			select 	@UserName = (select sLastUpdatedBy from inserted)
			if exists (select * from deleted)
				select @Type = 'U'
			else
				select @Type = 'I'
		end
	else
		begin
			select @Type = 'D'
			select 	@UserName = (select sLastUpdatedBy from deleted)
		end
	
	-- 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 sysAuditLog_T (AUDITsTableName, AUDITsPK, AUDITsFieldName, AUDITsPreviousValue, AUDITsNewValue, AUDITdtUpdateDate, AUDITsUserName, AUDITsActionType)'
			select @sql = @sql + 	' select ''' + @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 + 	',''' + @Type + ''''
			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
END

Original Code and theory came from somewhere on the web - probably Tek-tips. Thanks to whoever submitted it

Hope everyone is having a great day!

Thanks - Jennifer

 

[jzelhart]

FYI - I did the drop and replace on the table - the trigger works. This worries me...

Hope everyone is having a great day!

Thanks - Jennifer