question

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