MySQL Triggers last_insert_id()

Question

I have table 1 in which has id as autoincrement. Table 2 has a foreign key relationship to Table 1, in which I need that value generated from table 1 to be inserted into table 2

-- Trigger DDL Statements
DELIMITER $$

USE `baemer_emr`$$

CREATE TRIGGER `baemer_emr`.`after_insert_log` AFTER INSERT ON `baemer_emr`.`table1`
FOR EACH ROW
BEGIN
  INSERT INTO table2 VALUES (last_insert_id() , something);
END$$

It is working, but is displaying the number before. For example

Insert into table 1, id = 15. In table 2 returns 14. Any ideas?

Instead of `last_insert_id()` try using `new.id` or whatever you named your primary key column. — N.B., Oct 10 '11 at 15:50
@Skynight, don't put your email address in a question, unless you liked to get spammed. — Johan, Oct 10 '11 at 15:58
possible duplicate of [mysql getting last_insert_id() in a trigger](http://stackoverflow.com/questions/6499389/mysql-getting-last-insert-id-in-a-trigger) — mellamokb, Oct 10 '11 at 16:03
Possible duplicate of [mysql getting last\_insert\_id() in a trigger](http://stackoverflow.com/questions/6499389/mysql-getting-last-insert-id-in-a-trigger) — Jeff Puckett, Mar 08 '17 at 03:38

Johan · Answer 1 · 2011-10-10T16:05:45.970

7

In a trigger you have 2 virtual tables, called NEW and OLD You can use these virtual table to get the newly inserted values.
The OLD virtual table only works in the UPDATE and DELETE triggers obviously.
And the DELETE trigger does not have NEW table.

-- Trigger DDL Statements
DELIMITER $$

USE `baemer_emr`$$

CREATE TRIGGER after_insert_log AFTER INSERT ON table1
FOR EACH ROW
BEGIN
  INSERT INTO table2 VALUES (NEW.id, something);
END$$

Note that NEW.id _(assuming that id is a auto_increment field)_ will not yet be set in a before insert trigger, just one of those little pitfalls to avoid.

edited Oct 10 '11 at 16:05

answered Oct 10 '11 at 16:00

Johan

74,508
24
191
319

Will that NEW.id be affected if for example, there are 3 more transactions in the meantime? And instead of returning 12 returns 17? – Skynight Oct 10 '11 at 19:54
1

@Skynight In the `after` triggers NEW is read-only and the trigger runs `FOR EACH ROW` so you don't have to worry 'bout that :-). – Johan Oct 10 '11 at 19:58
1

thanks a lot and one more question...how can I tell the difference between NEW.id from table 1 or table 2? if they have the same column name for example @Johan – Skynight Oct 10 '11 at 19:59

MySQL Triggers last_insert_id()

1 Answers1

Linked