MySQL LAST_INSERT_ID() not updating due to AUTO_INCREMENT

Question

Possible Duplicate:
mysql getting last_insert_id() in a trigger

I have MySQL TRIGGER firing AFTER INSERT ON a table (my_table).

I wish to store the PRIMARY_KEY of the row effected by the TRIGGER in a variable using:

SET  @user_id =  LAST_INSERT_ID();

Which is backed by this stackoverflow link. And seems to be working.

However, each time it stores the value as 0.

From this stackoverflow link, I believe this is due to the fact that my_table is set to AUTO_INCREMENT.

SET  @user_id =  LAST_INSERT_ID();

What is the best practice to overcome this?

http://stackoverflow.com/questions/6499389/mysql-getting-last-insert-id-in-a-trigger — ethrbunny, Oct 30 '12 at 14:41
As @ethrbunny said, this is a dupe. Use new.id instead of last_insert_id(). — Ike Walker, Oct 30 '12 at 15:37
If there's no `AUTO_INCREMENT`, `LAST_INSERT_ID()` is pointless. — Álvaro González, Oct 30 '12 at 15:42

score 2 · Accepted Answer · answered Oct 30 '12 at 15:36

2

The LAST_INSERT_ID() function returns only autogenerated ID values. If you generated ID itself, then LAST_INSERT_ID() won't return it.

Also, trigger works in another session. But LAST_INSERT_ID() works only for current session - where new ID was inserted, e.g. -

-- NULL means that ID will be generated by server
INSERT INTO table1(id) VALUES(NULL);
SELECT LAST_INSERT_ID();
> 1

answered Oct 30 '12 at 15:36

Devart

1 Answers1