mysql getting last_insert_id() in a trigger

Question

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.

Goldoni

Answer 1

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

Ambagious

This seems to be working. That NEW keyword was exactly the sort of thing I was looking for. Thanks. – Goldoni