CODEWITHSUNDEEP
sql-servertriggers
Rory
Rory

Reputation: 41897

Check if only a specific column was updated in a sql server trigger?

I want to write a trigger that will execute some logic unless only one specific column was updated, e.g. if only the 'UpdateDate' column was updated then don't bother firing the trigger.

I know I can use UPDATE() and COLUMNS_UPDATED() but it seems difficult to write it in a maintainable way to check if it's just that one column updated. E.g. I could call UPDATE() against each of the other columns in the table but then if someone adds another column and doesn't modify the trigger it will break.

Upvotes: 1

Views: 3288

Answers (2)

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171559

For SQL Server 2005+ I would do it this way:

Assuming Column4 is the specific column you want to ignore, I would do something like this:

select *
into #changedRows
from (
   select Column1, Column2, Column3 from deleted 
   except 
   select Column1, Column2, Column3 from inserted
) x

if not exists (select * from #changedRows)
   return;

The advantage of this method is that it will correctly determine not just whether a column was updated, but whether the data was actually changed, which is usually all you care about.

Upvotes: 2

Rory
Rory

Reputation: 41897

I ended up coming up with this logic:

If

  • the column of interest was updated, and
  • COLUMNS_UPDATED() has only one bit set

then don't fire the rest of the trigger.

e.g. 

-- If columns_updated() is bigger than a bigint will take then 
-- just continue. We could deal with this better but not an issue for me.
if datalength(columns_updated())<63 begin 

    declare @colsUpdated bigint
    set @colsUpdated = cast( columns_updated() as bigint ) 

    -- if UpdateDate column was updated AND there was only a single column updated 
    -- don't run the rest of the trigger. See http://stackoverflow.com/a/4624295/8479
    -- for discussion of how to check if only one bit of a number is set. If only 
    -- one bit is set then only one col was updated, and we use update() to check 
    -- if it was UpdateDate.
    if update(UpdateDate) 
        and ( @colsUpdated & ( @colsUpdated-1 ) = 0 )
    begin 

        return;
    end 
end

Upvotes: 1

Related Questions