Trigger after insert points to the wrong last_insert_id()

Question

I am running this trigger

DELIMITER //
CREATE TRIGGER lestrigger
    AFTER INSERT ON examinations
   FOR EACH ROW
  BEGIN
    DECLARE the_last_inserted_id INT;
    SELECT LAST_INSERT_ID() INTO the_last_inserted_id;

END //
DELIMITER ;

After insert,the last_inserted_id variable holds the last_insert_id of the previous insert instead of the current.

To fix this,i did SELECT LAST_INSERT_ID()+1 INTO the_last_inserted_id; but this is not really a fix since i don't know why the trigger is not working as it should.There is a similar question here but i don't understand it.Should i always add 1 to my trigger like the way i have done it?.

"*[`LAST_INSERT_ID()`](http://dev.mysql.com/doc/en/information-functions.html#function_last-insert-id) (with no argument) returns a [`BIGINT`](http://dev.mysql.com/doc/en/integer-types.html) (64-bit) value representing the first automatically generated value that was set for an `AUTO_INCREMENT` column by the most recently executed `INSERT` statement to affect such a column.*" Thus you can't use it to get what you want; however, as the answer to which you have linked suggests, you can use the `NEW` table alias to access the value which has been assigned to your `AUTO_INCREMENT` column. — eggyal, Jul 22 '13 at 18:16

spencer7593 · Accepted Answer · 2013-07-24T15:54:58.077

No, don't add 1 to last_insert_id() in the trigger.

In a multi-user, multi-threaded environment, you have no guarantee (and no expectation) that this will get you the id value that was assigned to the row that was just inserted. (An INSERT statement can insert more than one row, and a value for ID can be supplied so that it is not auto-generated.)

If what you want is the value that was actually assigned to the id column of the row that was just inserted (whether that was auto-generated, or whether the INSERT statement inserted more than one row), the do this:

SET the_last_inserted_id = NEW.id;

That gets the actual value that was assigned to the row (in an AFTER UPDATE FOR EACH ROW trigger. In a BEFORE UPDATE ... FOR EACH ROW trigger, this value could actually be modified.)

The behavior you are observing isn't wrong; it's what we expect. The behavior of the LAST_INSERT_ID() function is well documented here:

http://dev.mysql.com/doc/refman/5.5/en/information-functions.html#function_last-insert-id

Trigger after insert points to the wrong last_insert_id()

1 Answers1