I've created a trigger to record what's been changed in a table. My problem is that sometimes I'm getting blank rows. The logic below records the date/time, username, name of column with changed data and the before and after version of the data. I would like it to not do this if the row is updated but none of the columns were changed.
Is it true that the trigger fires even if none of the values in any of the columns have changed?
If that's true, could I put my string into a variable, then test the variable to see if there's a comma (which I put a the end of each before/after part of the remarks)? I haven't used a variable in this way before, if that's the best way to go, could someone provide a line or two of pseudo code that would implement this?
Or does someone have another idea about how to not write information about these updates where none of the columns have changed into my ParcelUpdateLog table?
ALTER TRIGGER [dbo].[Appeal_Log_Update]
SET NOCOUNT ON;
insert into ParcelUpdateLog(remarks)
select convert(varchar(20),getdate(),100) + ' ' + replace(user_name(),'EP\','') +
' UPDATED Appeal row ParcelNo=' + d.ParcelNo + ' (' + cast(d.TaxYear as char(4)) + ') LevelID=' + d.LevelID + ' changed ' +
case when d.SeqNo=i.SeqNo then '' else 'SeqNo from ' + cast(d.SeqNo as varchar(3)) + '=>' + cast(i.SeqNo as varchar(3)) + ', ' end +
case when d.LevelID=i.LevelID then '' else 'LevelID from ' + d.LevelID + '=>' + i.LevelID + ', ' end +
case when d.FiledBy=i.FiledBy then ''
else 'FiledBy from ' + d.FiledBy + '=>' + i.FiledBy + ', ' end