AFTER UPDATE OF trigger is skipped when its column appears later in the SET list

An AFTER UPDATE OF b trigger fires when b is the first assignment in a multi-column UPDATE, but is silently skipped when the same assignment appears second. Both updates succeed and assign the requested values.

Setup and reproduction

Run the following in one client session:

CREATE DATABASE trigger_order;

USE trigger_order;

CREATE TABLE t1 (a INT, b INT);

INSERT INTO t1 VALUES (0,0);

CREATE TRIGGER update_b AFTER UPDATE OF b

ON t1 FOR EACH ROW

SET @hits=@hits+1;

SET @hits=0;

UPDATE t1 SET b=1,a=1;

SELECT 'b_first',@hits;

SET @hits=0;

UPDATE t1 SET a=2,b=2;

SELECT 'b_second',@hits;

SELECT 'row',a,b FROM t1;

DROP DATABASE trigger_order;

The assignments use independent integer literals, so changing their order does not introduce an expression dependency.

Decisive observation

The recorded values were:

b_first   1

b_second  0

row       2  2

The first update invokes the trigger once. After resetting the counter, the second update changes b from 1 to 2 but does not invoke the same trigger. The final row (2,2) confirms that both assignments in the second statement completed.

Semantic mismatch

Both statements update column b, so AFTER UPDATE OF b must fire once for each statement regardless of the assignment's position in the SET list. Assignment order should not determine whether the trigger's column matcher recognizes b.

Source-supported mechanism

The supplied source evidence identifies the updated-column matcher in sql/sql_trigger.cc: it constructs columns_it outside the loop over updated fields and consumes the iterator without rewinding it for each field. A matching column that appears after another assignment can therefore be missed.

This occurs when an UPDATE OF trigger monitors a column that follows another column in a multi-column UPDATE SET list.