question

David 2 1 avatar image
David 2 1 asked

Trigger To Update Table Column of Column Updates

Hi there, I'm writing a trigger that whenever a user updates the specified columns of a table a record of this update, the columns updated and the before and after values, are stored within the same row table. I've probably written it overtly complex and it could more than likely be written better however I am having an issue with the last part of the trigger. Most noteably the very last update part: SET AMENDED_DETAILS = (CONVERT(VARCHAR(5000),COALESCE(@cols,NULL,'')) + ' ' + CONVERT(VARCHAR(5000),COALESCE(AMENDED_DETAILS,NULL,''))) This line does not appear to append the old amended details onto the new changes? Any ideas or help would be greatly appreciated. The trigger text is: ALTER TRIGGER HISTORY_AMENDED ON DBO.HISTORY FOR UPDATE AS IF UPDATE(MODEL) OR UPDATE(MANUFACTURER) BEGIN declare @amend int declare @prop int declare @order int declare @client int set @amend = NULL exec testdb.testuser.get_next_number 'amendno', @amend OUTPUT set @prop = (select actlineno from inserted) set @order = (select vehorderno from inserted) set @client = (select clientno from inserted) declare @col1 varchar(30) set @col1 = null IF NOT EXISTS(SELECT * FROM SYSOBJECTS WHERE XTYPE='u' AND NAME='AMENDMENTS_LOG') CREATE TABLE AMENDMENTS_LOG( AMENDNO int, ACTLINENO int, VEHORDERNO int, CLIENTNO int, DATE_AMENDED datetime, COLUMN_AMENDED varchar(30), OLD_VALUE varchar(2000), NEW_VALUE varchar(2000)) declare cur_1a cursor fast_forward for select column_name from information_schema.columns where table_name = 'HISTORY' and column_name in ( 'MODEL','MANUFACTURER') open cur_1a fetch next from cur_1a into @col1 while @@fetch_status = 0 begin exec(' declare @val1 varchar(2000) set @val1 = null declare cur_2b cursor fast_forward for select ' + @col1 + ' from HISTORY where ACTLINENO = ' + @prop + ' and AMENDED_VERSION = (select max(AMENDED_VERSION) from HISTORY where ACTLINENO = ' + @prop + ') open cur_2b fetch next from cur_2b into @val1 while @@fetch_status = 0 begin insert into AMENDMENTS_LOG(AMENDNO, ACTLINENO, VEHORDERNO, CLIENTNO, DATE_AMENDED, COLUMN_AMENDED, OLD_VALUE, NEW_VALUE) select ' + @amend + ', ' + @prop + ', ' + @order + ', ' + @client + ', GETDATE(), ''' + @col1 + ''' , ' + @col1 + ', @val1 from HISTORY where ' + @col1 + ' @val1 and ACTLINENO = ' + @prop + ' and AMENDED_VERSION = (select max(AMENDED_VERSION)-1 from HISTORY where ACTLINENO = ' + @prop + ') fetch next from cur_2b into @val1 end close cur_2b deallocate cur_2b ') fetch next from cur_1a into @col1 end close cur_1a deallocate cur_1a END BEGIN DECLARE @cols VARCHAR(8000) SELECT @cols = COALESCE(@cols + ', ', '') + CAST(COALESCE(column_amended,NULL,'') AS VARCHAR(100)) + '. ' + CAST(COALESCE(old_value,NULL,'') AS VARCHAR(100)) + '. ' + CAST(COALESCE(new_value,NULL,'') AS VARCHAR(100)) + '. ' FROM AMENDMENTS_LOG WHERE ACTLINENO = @prop AND AMENDNO = (SELECT MAX(AMENDNO) FROM AMENDMENTS_LOG WHERE ACTLINENO = @prop) UPDATE HISTORY SET AMENDED_DETAILS = (CONVERT(VARCHAR(5000),COALESCE(@cols,NULL,'')) + ' ' + CONVERT(VARCHAR(5000),COALESCE(AMENDED_DETAILS,NULL,''))) WHERE ACTLINENO = @prop AND AMENDED_VERSION = (SELECT MAX(AMENDED_VERSION) FROM HISTORY WHERE ACTLINENO = @prop) END
sql-server-2000tsqlupdatetrigger
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

This site runs off of votes. Please mark all helpful answers by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate that by clicking on the check mark next to that answer.
0 Likes 0 ·
KenJ avatar image
KenJ answered
What if somebody updates more than one row? This might not work so well: set @prop = (select actlineno from inserted) For the problem you are having, is it just overwriting AMENDED_DETAILS with @cols?
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks for your reply Ken. No only one record will ever be updated at a time. Yes the problem is that AMENDED_DETAILS is being overwritten with @cols, whereas I'm just wanting @cols appended to the front of AMENDED_DETAILS. I thought it was a problem with AMENDED_DETAILS being a TEXT datatype but I've tried everything from changing this columns type to CAST and CONVERTING it. Really lost as to why @col wont append.
0 Likes 0 ·
It's been ages, but 'm not convinced that SQL Server 2000 will let you to append two varchar(5000) strings to get a 10,000 character string. Since AMENDED_DETAILS is a text column, you might have some fun with the READTEXT, WRITETEXT and UPDATETEXT functions - http://msdn.microsoft.com/en-us/library/aa238511(v=sql.80).aspx These memories are not pleasant :)
0 Likes 0 ·
Venkataraman avatar image
Venkataraman answered
Having this big trigger logic might increase the transaction time. You can go for Change Data Capture if possible as it is asynchronous and will not affect the DML operation.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks, unfortunately it's a legacy 2000 system.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.