IF UPDATE in trigger returns true when no rows were affected Why is the trigger executing the update(col1) block in the following:

USE tempdb
go
CREATE TABLE t1(id INT, col1 VARCHAR(10))
go
INSERT INTO dbo.t1( id, col1) VALUES(1,'aa'),(2,'sdf'),(3,'fg')
go
CREATE TRIGGER r_test ON t1 after UPDATE
AS
PRINT 'Trigger'
IF UPDATE(col1)
    PRINT 'col updated'
GO

UPDATE t1 SET col1='werwer' WHERE id=4


output


  col updated


I understand why the trigger executed but since id = 4 doesn't exist, shouldn't UPDATE(col1) be returning false?
Because IF UPDATE(col1) only checks if col1 was referenced in the UPDATE statement. It does not check how many rows were affected, nor does it check if the value has actually changed.

To not get the trigger to fire when zero rows are affected:

CREATE TRIGGER dbo.r_test 
  ON dbo.t1 after UPDATE
AS
BEGIN
  IF @@ROWCOUNT &gt; 0
  BEGIN
    ...
  END
END


Note that MERGE may complicate this - @@ROWCOUNT can be non-zero even if no rows are updated, only inserted/deleted (more details here).

Instead you can:

CREATE TRIGGER dbo.r_test 
  ON dbo.t1 after UPDATE
AS
BEGIN
  IF EXISTS (SELECT 1 FROM inserted)
     AND EXISTS (SELECT 1 FROM deleted)
  BEGIN
    ...
  END
END


To determine if the value has changed:

IF EXISTS
(
  SELECT 1 
    FROM inserted AS i
    INNER JOIN dbo.t1 ON i.id = t1.id
    WHERE i.col1 &lt;&gt; t1.col1
);


This is grossly simplified; NULLs will complicate this, as will columns with encrypted values.

Also, please always use the schema prefix.