MySQL Bugs: #23771: AFTER UPDATE trigger not invoked when there are no changes of the data

Bug #23771	AFTER UPDATE trigger not invoked when there are no changes of the data
Submitted:	30 Oct 2006 15:04	Modified:	15 Mar 2008 10:37
Reporter:	Georgi Kodinov
Status:	Closed
Category:	Server: SP	Severity:	S3 (Non-critical)
Version:	5.0.27/5.1BK	OS:	Linux (Suse 10.0 32-bit)
Assigned to:	Davi Arnaut	Target Version:	5.0+
Triage:	D2 (Serious)

Description:
AFTER UPDATE trigger in MySQL won't fire on UPDATE if there are no real changes on the
row (contrary to how BEFORE UPDATE behaves).

How to repeat:
Here's a script to demonstrate (both selects must return the same value):

CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a));
CREATE TRIGGER trg1 BEFORE UPDATE ON t1 FOR EACH ROW set @a=OLD.b;
CREATE TABLE t2 (a INT, b INT, PRIMARY KEY (a));
CREATE TRIGGER trg2 AFTER UPDATE ON t2 FOR EACH ROW set @b=OLD.b;
set @a=1212;
INSERT INTO t1 VALUES (45, 12);
UPDATE t1 SET b= b;
SELECT @a;
@a
12
set @b=1212;
INSERT INTO t2 VALUES (45, 12);
UPDATE t2 SET b= b;
SELECT @b;
@b
1212
DROP TABLE t1,t2;

Suggested fix:
make AFTER UPDATE triggers run even if there's no change.

Thank you for the bug report.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/42102

ChangeSet@1.2546, 2008-02-12 09:44:27-02:00, davi@mysql.com +3 -0
  Bug#23771 AFTER UPDATE trigger not invoked when there are no changes of the data
  
  The problem is that AFTER UPDATE triggers will fire only if the
  new data is different from the old data on the row. The trigger
  should fire regardless of whether there are changes to the data.
  
  The solution is to fire the trigger on UPDATE even if there are
  no changes to the value (because the value is the same).

Pushed in 5.1-runtime

Pushed into 5.1.24-rc

Pushed into 6.0.5-alpha

Documented in the 5.1.24 and 6.0.5 changelogs as follows:

        An AFTER UPDATE trigger was not invoked when the UPDATE did not make any
        changes in the table. Now AFTER UPDATE triggers behave the same in this
        regard as do BEFORE UPDATE triggers, which are invoked whether the
        UPDATE makes any changes in the table or not.

Also tagged as an important change in behaviour.

Also documented fix for 5.1.23-ndb-6.3.11.