MySQL触发器last_insert_id() [英] MySQL Triggers last_insert_id()

问题描述

我有一个表1，其中ID为自动增量. 表2与表1具有外键关系，其中我需要将从表1生成的值插入表2

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

插入表1，id =15.在表2中返回14.有什么想法吗?

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

推荐答案

在触发器中，您有2个虚拟表，分别称为NEW和OLD 您可以使用这些虚拟表来获取新插入的值.
OLD虚拟表仅在UPDATE和DELETE触发器中明显起作用.
并且DELETE触发器没有NEW表.

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$$

请注意，NEW.id _(假定id是一个auto_increment字段)_将尚未设置在before insert触发器中，只是要避免的小陷阱之一.

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.

这篇关于MySQL触发器last_insert_id()的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！