Microsoft KB Archive/64238

= PRB: IF UPDATE Trigger with No Rows Changed =

Article ID: 64238

Article Last Modified on 2/14/2005

-

APPLIES TO


 * Microsoft SQL Server 4.21a Standard Edition
 * Microsoft SQL Server 6.0 Standard Edition
 * Microsoft SQL Server 6.5 Standard Edition
 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q64238



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 &quot;INSERTED.&quot;



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.



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 &quot;Trigger t1 has detected an update of col2&quot; 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 KB64238

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.