mysql getting last_insert_id() in a trigger

mysql triggers lastinsertid
Steinthor.palsson picture Steinthor.palsson · Jun 27, 2011 · Viewed 17.2k times · Source

It's my understanding that when you call last_insert_id() it goes by connections, so you'll get the id of the last row inserted on the same connection where last_insert_id() is called, right?

So what if I call last_insert_id() in an 'AFTER INSERT' trigger?

What I want to do is basically this

DELIMITER $$
CREATE TRIGGER sometrigger
AFTER INSERT ON sometable
BEGIN
  INSERT INTO anothertable (id, lastup) VALUES (last_insert_id(), NOW());
END $$

It's very important that the id in 'anothertable' is the same as in 'sometable' Would this work or should I create a stored procedure instead that inserts into both tables?

Or possibly there is some, in the trigger, to get the values from the insert statement that caused the trigger to fire? I haven't found anything about that.

Answer

Femi picture Femi · Jun 28, 2011

You should be able to use NEW.{id column name} to refer to the last insert id (so for example NEW.id if the auto increment column is called id.)

Related questions

How to program a MySQL trigger to insert row into another table?

I'm looking to create a MySQL trigger on a table. Essentially, I'm creating an activity stream and need to log actions by users. When a user makes a comment, I want a database trigger on that table to fire and: …

Read More
MySQL Trigger after update only if row has changed

Is there any possibility to use an "after update" trigger only in the case the data has been REALLY changed. I know of "NEW and OLD". But when using them I'm only able to compare columns. For example "NEW.count &…

Read More
MySQL Trigger - Storing a SELECT in a variable

I have a trigger in which I want to have a variable that holds an INT I get from a SELECT, so I can use it in two IF statements instead of calling the SELECT twice. How do you declare/…

Read More