Trigger problem - adding dupes to archive table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Trigger problem - adding dupes to archive table

Author

Topic

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2009-10-06 : 14:46:59

Hello -

I have a trigger on a table that I am using to populate an archive table. The archive table will be used database-wide to track changes to the tables. Essentially, I have to track the old and new values for any changes to a table.

When my trigger fires and I update only a single column or all the columns, the archive table gets the correct values/rows. For example, if my update is for a single column, ony one row gets added to the archive table. If I update all columns (and suppose there are 12 columns), then I get 12 rows in the archive table.

The problem is when I update a subset of the columns (2 or 5 or whatever). When I do this, I get multiple rows of duplicates for one of the columns.

Below is my table and trigger code.


CREATE TABLE [dbo].[TestTrigger](
	[PKID] [int] IDENTITY(1,1) NOT NULL,
	[TheName] [varchar](50) NULL,
	[TheNumber] [int] NULL,
	[TheDate] [datetime] NULL,
	[TheBit] [bit] NULL,
	[TheNewCol] [varchar](50) NULL
) ON [PRIMARY]


CREATE TABLE [dbo].[Archive_TestTrigger](
	[PKID] [int] IDENTITY(1,1) NOT NULL,
	[TableName] [varchar](30) NULL,
	[PrimaryKey] [int] NULL,
	[ColumnName] [varchar](30) NULL,
	[ColumnType] [varchar](10) NULL,
	[OldValue] [varchar](50) NULL,
	[NewValue] [varchar](50) NULL,
	[createDt] [datetime] NULL
) ON [PRIMARY]


Create Trigger [dbo].[trg_Update_Test] 
	on [dbo].[TestTrigger] 
	for update
as
declare @bit int ,
	@field int ,
	@char int,
	@colName nvarchar(50)
	
declare @sqlCmd nvarchar(2000)
declare @TableName nvarchar(30)
set @TableName = 'TestTrigger'

select @field = 0

Select * into #Inserted from inserted
Select * into #Deleted from deleted


while @field < (select max(colid) 
	from syscolumns 
	where id = (select id from sysobjects where name = 'TestTrigger'))

begin

	select @field = @field + 1
	select @bit = (@field - 1 )% 8 + 1
	select @bit = power(2,@bit - 1)
	select @char = ((@field - 1) / 8) + 1
	

	select @char as AtChar, @field as AtField, @bit as AtBit  -- debug code to check the bits that are tested.

	if substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0
		set @colName = (select --@field, 
			name as ColumnName
		from 
			syscolumns 
		where 
			colid = @field 
			and id = (select id from sysobjects where name = 'TestTrigger'))

		set @sqlCmd = 'INSERT INTO dbo.Archive_TestTrigger  '
		set @sqlCmd = @sqlCmd + '(TableName, PrimaryKey, ColumnName, ColumnType, OldValue, NewValue, CreateDt)  '		
		set @sqlCmd = @sqlCmd + ' SELECT ' + quotename(@TableName, '''') + ' AS TableName, ' + 'p.PKID as PrimaryKey, '
		set @sqlCmd = @sqlCmd + quotename(@colName, '''') + ' AS ColumnName, space(5) as columnType, '
		set @sqlCmd = @sqlCmd + 'd.' + @colName + ' AS OldValue, i.' + @colName + ' AS NewValue,'
		set @sqlCmd = @sqlCmd + 'GetDate() as CreateDt '
		set @sqlCmd = @sqlCmd + ' FROM dbo.TestTrigger as p INNER JOIN #Deleted as d on p.pkID = d.pkID '
		set @sqlCmd = @sqlCmd + ' INNER JOIN #inserted as i on p.pkID = i.pkID'

		execute sp_executesql @sqlCmd 
end

If I execute an update like this:


update dbo.TestTrigger
	Set 
		TheName = 'Another updated name value',
		TheNewCol = 'Some updated text'
	where
		pkID = 1

The following is the result in the Archive table, which shows 4 rows for the TheName column and one for TheNewCol:



PKID	TableName	PrimaryKey	ColumnName	ColumnType	OldValue	NewValue	createDt
1	TestTrigger	1	TheName	     	Updated name value	Another updated name value	2009-10-06 11:44:35.600
2	TestTrigger	1	TheName	     	Updated name value	Another updated name value	2009-10-06 11:44:35.600
3	TestTrigger	1	TheName	     	Updated name value	Another updated name value	2009-10-06 11:44:35.603
4	TestTrigger	1	TheName	     	Updated name value	Another updated name value	2009-10-06 11:44:35.603
5	TestTrigger	1	TheNewCol	     	Are we still getting lots of extra rows?	Some updated text	2009-10-06 11:44:35.610

If anyone has any thoughts here, I'd appreciate it.

Thanks,
- will

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-10-06 : 15:14:51

The problem with your code is that you are missing BEGIN/END keywords for your IF statement:
if substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0
BEGIN
....
END
But I'm not sure that you need all that dymamic sql and system table stuff. I think there's an easier way.

Be One with the Optimizer
TG

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2009-10-06 : 16:44:45

Hi.

Thanks for catching that problem. You are correct, and adding the Begin/End fixes the duplicate problem.

As for the dynamic sql stuff, well, some of the tables I have to archive have 40 to 50 columns and I didn't want to write a series of 40 or 50 IF UPDATE('col1')...IF UPDATE(col49) etc. So, using this challenging bit of code I found on another web site to use the Columns_Updated() seems to handle it all pretty well.

Thanks so much for your help.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-10-06 : 16:52:42

I guess my only concern is the frequent selects from system tables. You could end up blocking or being blocked by administrative tasks. You could use similar code to generate your trigger code so you still don't have to do all the typing but the trigger has all the columns explicitly listed - and no dynamic sql either.

Be One with the Optimizer
TG

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2009-10-07 : 11:57:12

Thanks for the excellent points. It would be more understandable and maintainable if I used the single UPDATE(column) statement rather than the looping code.

thanks again
- will

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources