Bug #23771 AFTER UPDATE trigger not invoked when there are no changes of the data
Submitted: 30 Oct 2006 14:04 Modified: 15 Mar 2008 9:37
Reporter: Georgi Kodinov Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S3 (Non-critical)
Version:5.0.27/5.1BK OS:Linux (Suse 10.0 32-bit)
Assigned to: Davi Arnaut CPU Architecture:Any
Triage: D2 (Serious)

[30 Oct 2006 14:04] Georgi Kodinov
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.
[30 Oct 2006 15:58] Miguel Solorzano
Thank you for the bug report.
[12 Feb 2008 11:44] Bugs System
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).
[19 Feb 2008 13:42] Davi Arnaut
Pushed in 5.1-runtime
[3 Mar 2008 18:18] Bugs System
Pushed into 5.1.24-rc
[3 Mar 2008 18:18] Bugs System
Pushed into 6.0.5-alpha
[15 Mar 2008 9:37] Jon Stephens
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.
[30 Mar 2008 0:08] Jon Stephens
Also documented fix for 5.1.23-ndb-6.3.11.