How do triggers affect last_insert_id()?

Question

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?

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;
/