Checking if something has changed in a trigger

Question

I have a need to monitor a subset of fields on a table and perform a task when one of them changes.

I am using a trigger on the table update which and then am looking at the changes as follows:

-- join the deleted and inserted to get a full list of rows
select * into #tmp from (select * from inserted union select * from deleted) un
-- select a count of differing rows, > 1 means something is different
select distinct count(*) from #tmp

This is fine and a count of 2 or more means something is different on single line updates. Issue is if I am doing a multiple line update then this breaks down.

Is there a way I can get this to work for a multi line update or do I need to try a different approach completely.

Answer 1

You could do something like this (syntax completely untested)

IF NOT UPDATE(col) 
 RETURN

SELECT inserted.key, inserted.col as i_col,  deleted.col as d_col
INTO #interestingrows
 FROM inserted JOIN deleted on inserted.key = deleted.key 
    and inserted.col <> deleted.col /*If col is nullable cater for that as well*/

IF @@ROWCOUNT=0
 RETURN

 /*Process contents of  #interestingrows*/

Answer 2

I ended up with a fairly simple solution. I wrote an additional loop around the check that did the check per line in inserted.

        -- get a list of updated line id's
        select field1 as id into #loop from inserted 



    -- loop through all the id's and do a compare
    while (select count(*) from #loop) > 0 begin
        select top 1 @id = id from #loop

        select * into #tmp from (select * from inserted where field1 = @id union 
                                 select * from deleted where field1 = @id) un

        -- do a select ditinct to count the differing lines.
        if (select distinct count(*) from #tmp) > 1 begin
            -- the 2 lines don't match, so mark for update
            update test1 set flag = 1 where field1 = @id
        end
        drop table #tmp

        delete #loop where id = @id
    end