trigger records updates even if no data changed

GordonPrince
GordonPrince used Ask the Experts™
on
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] 
ON  [dbo].[Appeals] 
AFTER UPDATE
AS 
BEGIN
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

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Replace column_name in the below line to identify modified logs and update events in ParcelUpdateLog table.

UPDATE(column_name)
ALTER TRIGGER [dbo].[Appeal_Log_Update] 
ON  [dbo].[Appeals] 
AFTER UPDATE
AS 
BEGIN
SET NOCOUNT ON;

IF UPDATE(column_name)
BEGIN
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
END
GO

Open in new window

Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
well,>IF UPDATE(column_name)will only check if the column was in the SET clauses, but not if the value actually changed..you will have to join INSERTED and DELETED and compare the value(s) of INSERTED vs DELETED

Author

Commented:
RE: you will have to join INSERTED and DELETED and compare the value(s) of INSERTED vs DELETED
-------------------------------------------------------------
I'm examining all the inserted and deleted versions of the columns. The issue is I don't want to insert ANYTHING in my ParcelUpdateLog table unless ONE OR MORE of the columns has changed. I would think either
1. put the whole "select convert..." into a variable, then examine the variable and if it contains a comma then at least one of the columns changed. So then insert the variable into the ParcelUpdateLog table. Or
2. In my long case statement, if I want to record the column's old value and new value, set a boolean variable = true. Then insert into ParcelUpdateLog only if the boolean variable is true.
3. This seems like the worst choice -- after doing the insert into ParcelUpdateLog, delete the rows from ParcelUpdateLog that don't have a comma in them. This would work, but it doesn't seem right to insert the row, then delete it some of the time. 1. or 2. seem like better approaches.
Any thoughts?
I'm sure I can implement #3, but I'm not sure how to do #1 or #2. That's what I'm looking for assistance with, I think.
Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
Commented:
let me give you the rough basics:
create trigger trg_update
 on yourtable
for update
as
begin
  insert into ...
   SELECT ...
     FROM INSERTED i
     JOIN DELETED d
       ON i.key_col = d.key_col
    WHERE ( i.col1 IS NULL AND d.col1 IS NOT NULL )
       OR ( i.col1 IS NOT NULL AND d.col1 IS NULL )
       OR ( i.col1 <> d.col1 )
 
       OR ( i.col2 IS NULL AND d.col2 IS NOT NULL )
       OR ( i.col2 IS NOT NULL AND d.col2 IS NULL )
       OR ( i.col2 <> d.col2 )
    ... etc for the other columns ...
   


end

Open in new window

Author

Commented:
That looks like it will work. Thanks.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial