MySQL Bugs: #55353: RFE: Make inserted row readable in it's AFTER INSERT trigger

Bug #55353	RFE: Make inserted row readable in it's AFTER INSERT trigger
Submitted:	19 Jul 2010 1:25	Modified:	23 Jul 2010 2:24
Reporter:	Ondra Zizka	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.1.48	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
In an AFTER INSERT TRIGGER, the row which was just inserted is not in the table.

How to repeat:
CREATE TABLE t1 ( a INT, b INT ) $$
CREATE TABLE log ( str VARCHAR(255) NOT NULL ) $$
CREATE TRIGGER t1_after_insert AFTER INSERT ON t1
FOR EACH ROW BEGIN  body: BEGIN
  SET @sum = (SELECT SUM(b) FROM t1 WHERE a = NEW.a);
  INSERT INTO log SET str = @sum;
END body; END $$
INSERT INTO t1 SET a = 1, b = 100;
INSERT INTO t1 SET a = 2, b = 200;

SELECT * FROM log;

In the log, you can see that at the time of running the TRIGGER, the newly inserted row is not in the table yet.

Suggested fix:
Since the name is AFTER INSERT, the semantics should be that the newly inserted row is already in the table at the time of processing the trigger.

You can't modify the data using `NEW.a` anyway.

I guess this is blocked by MySQL's architecture, but it would be nice to have this fixed.

Thank you for the report.

what SELECT * FROM log outputs in your environment?

I get:

SELECT * FROM log;
str
100
200

Which means new row is in table during trigger run.

Oh, really. Which means that in search of a simple example, I simplified it too much. So it must be something specific to my case.

I am closing for now to keep bugzilla clean, and will eventually reopen if I happen to come up with a better test case.