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))
INSERT INTO TriggerTest VALUES (1, 'Hello')
CREATE TRIGGER tr_TriggerTest
AS BEGIN SET NOCOUNT ON;
PRINT 'The trigger fired'
PRINT 'The trigger did not fire'
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:
UPDATE() returns TRUE regardless of whether an INSERT or UPDATE attempt is successful.
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