Great question. It took me thorugh several layers. I had to realize that the trigger could never reach the "did not fire" branch of the if statement. Then, I was fooled (as were many, apparently) by the notion that a null-change would suppress the trigger. But then, a bit of experimentation led to deeper understanding.

Q: If it "didn't fire", how would it be running the IF statement?

A: Only if there were another column to be affected. This code does return "The Trigger did not fire" even though it did because the VALUE column was not touched. -- See a trigger say that it didn't fire....

-- (actually, the trigger fires, but internally it sees

-- that the tested field VALUE wasn't affected)

CREATE TABLE TriggerTest (Value int, Text char(10))

GO

INSERT INTO TriggerTest VALUES (1, 'Hello')

GO

CREATE TRIGGER tr_TriggerTest

ON TriggerTest

AFTER UPDATE

AS BEGIN SET NOCOUNT ON;

IF UPDATE(Value)

PRINT 'The trigger fired'

ELSE

PRINT 'The trigger did not fire'

END

GO

UPDATE TriggerTest SET Text = Text

Second, and to the point of several posts here, the trigger fires upon "an attempt" to update the table, but determining what constitutes an update attempt is a bit tricky. Microsoft's documentation is slightly misleading when it says in the link provided:

Actually, it fires even if no rows are found to update, but an error will prevent it from firing, so an unsucessful attempt in that case won't return TRUE. By example, this code returns "The trigger fired" even though it effects no change to the database (not even a wash of updating a field to its original value):--See trigger fire with 0 rows affected

UPDATE TriggerTest SET Value = Value

where value = 7 ...but if there's an error, as forced in the following statement, SQL Server doesn't consider the update to have been attempted and so does not fire the trigger.--See an error prevent the update and thereby the trigger

UPDATE TriggerTest SET Value = 'Value'

----

edit: minor typo