PRB: IF UPDATE Trigger with No Rows Changed

ID: Q64238

The information in this article applies to:

Microsoft SQL Server for OS/2, version 4.2

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

Page 303 of the "Microsoft SQL Server Transact-SQL User's Guide" for version 4.2 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

Keywords : kbother SSrvStProc SSrvServer
Version : 4.2
Platform : OS/2
Issue type :