CODEWITHSUNDEEP
mysqltriggerslastinsertid
Steinthor.palsson
Steinthor.palsson

Reputation: 6496

mysql getting last_insert_id() in a trigger

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.

Upvotes: 13

Views: 20189

Answers (1)

Femi
Femi

Reputation: 64700

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.)

Upvotes: 20

Related Questions