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 "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 KB64238
