ID Number: Q64238
1.00 1.10 1.11 4.20
OS/2
Summary:
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
Additional reference words: 1.00 1.10 1.11 4.20