Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am unable to test this right now, however I'm curious about the answer.

If I have a trigger on table A that writes a new record to table B, will LAST_INSERT_ID() tell me the updated ID for A or B? I'm curious about both MySQL and PHP, but my understanding is that PHP simply calls MySQL's implementation.

How do I get the last inserted ID for B if it normally returns A, and vice-versa?

share|improve this question

1 Answer 1

From mysql documentation

The effect of a stored routine or trigger upon the value of LAST_INSERT_ID() that is seen by following statements depends on the kind of routine:
....
For stored functions and triggers that change the value, the value is restored when the function or trigger ends, so following statements do not see a changed value. (Before MySQL 5.0.12, the value is not restored and following statements do see a changed value.)

Thus whatever happens inside the trigger[s] on A does not affect value returned by LAST_INSERT_ID() executed outside of trigger's body. Withing the trigger body LAST_INSERT_ID will reflect inserts you made to another table. To get last id for B you need to store the value of LAST_INSERT_ID() somewhere(new table, or a new column in A), for instance,

delimiter /

CREATE trigger biA before insert on A
for each row

begin
 insert into B(name) values('aaa');
 set new.b_id := (select last_insert_id());
end;
/
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.