PRB: IF UPDATE Trigger with No Rows Changed (64238)

SYMPTOMS

A trigger has been defined using the IF UPDATE(ColName) clause. When attempting an update on the column, even if the update statement does not actually affect any rows, the statements in the IF UPDATE block get executed. IF UPDATE should be true only for columns actually updated by a query; however, it returns true when no rows were updated.

CAUSE

The T-SQL reference states that IF UPDATE is used to test whether the specified column has been modified. IF UPDATE actually returns true whenever the specified column is included in an update statement. When an INSERT or UPDATE statement is executed, the affected rows are added to a special table called "INSERTED."

WORKAROUND

By adding the line

   and (select count(*) from inserted) > 0

just after the IF UPDATE clause, your trigger will be invoked only when at least one row is actually modified. Below is a SQL script that shows how this could be implemented.

MORE INFORMATION

Script for Trigger Using IF UPDATE

   use pubs
   go

   create table testtable
   (col1   int,
    col2   int,
    col3   int)
   go

   insert into testtable values( 5, 0, 0)
   insert into testtable values(10, 0, 0)
   insert into testtable values(15, 0, 0)
   insert into testtable values(20, 0, 0)
   go

   create trigger t1
   on testtable
   for update as
   if update(col2)
   and (select count(*) from inserted) > 0
   begin
   print "Trigger t1 has detected an update of col2"
   update testtable
   set col3 = col3 + 10
   end
   go

   select * from testtable
   go

   update testtable
   set col2 = col2 + 1
   where col1 > 0
   go

   select *  from testtable
   go

   update testtable
   set col2 = col2 + 1
   where col1 > 10
   go

   select *  from testtable
   go

   update testtable
   set col2 = col2 + 1
   where col1 > 20
   go

   select *  from testtable
   go

   drop table testtable
   go