CODEWITHSUNDEEP
phpmysql
Skynight
Skynight

Reputation: 537

MySQL Triggers last_insert_id()

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?

Upvotes: 2

Views: 2365

Answers (1)

Johan
Johan

Reputation: 76567

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.

Upvotes: 7

Related Questions