Tao Klerks (12/2/2009)
Right - but even if SQL Server was not doing any update at all (if there were no rows in the table, or if the WHERE clause excluded all rows), it would still consider that the column was updated.
Now THAT's something I didn't know about but should have. Perhaps the question would have been better if the UPDATE statement was:
UPDATE TriggerTest
SET Value = VALUE
WHERE VALUE = 0;
In this case the trigger still fires, and more importantly "IF UPDATE(Value)" returns true, even though no rows were updated, so actually no values were updated. (Yes, in the original version the Value column is updated from 1 to 1) I think the output text is a bit misleading though. I would have put "Value field was updated" and "Value field was not updated" in the IF block, and another line before the block for "Trigger Fired".
e.g.
CREATE TABLE TriggerTest (Value int)
GO
INSERT INTO TriggerTest VALUES (1)
GO
CREATE TRIGGER tr_TriggerTest
ON TriggerTest
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
PRINT 'The Trigger Fired'
IF UPDATE(Value)
BEGIN
PRINT 'The Value column was updated'
select * FROM INSERTED
END
ELSE
BEGIN
PRINT 'The Value column was not updated'
END
END
GO
--Set Value equal to itself
UPDATE TriggerTest
SET Value = VALUE
WHERE VALUE = 0;
DROP TABLE TriggerTest;