MYSQL触发器在同一个表中插入列值

Question

我有一个 mysql Innodb 表“classrooms_subjects”为

id|classroom_id|subject_id

classroom_id 和 subject_id 是复合键。每当我插入包含classroom_id和subject_id的行时，我的id字段就会插入为0。

现在我想创建一个触发器，它将输入 id 字段作为last_inserted_id()+1。

此外，我需要处理一次插入的多条记录。我的触发器如下:

CREATE TRIGGER `increment_id` AFTER INSERT ON `classrooms_subjects`
 FOR EACH ROW BEGIN
UPDATE classrooms_subjects
SET classrooms_subjects.id = LAST_INSERT_ID() + 1 WHERE id=0;
END

当我插入记录时，出现以下错误:“无法更新触发器中的表，因为它已被调用此触发器的语句使用

Answer 1

一般信息:在触发器内使用更新语句是不正确的。

最好使用插入前触发器并简单地使用 NEW.id 分配列的值

http://dev.mysql.com/doc/refman/5.7/en/trigger-syntax.html

A column named with OLD is read only. You can refer to it (if you have the SELECT privilege), but not modify it. You can refer to a column named with NEW if you have the SELECT privilege for it. In a BEFORE trigger, you can also change its value with SET NEW.col_name = value if you have the UPDATE privilege for it. This means you can use a trigger to modify the values to be inserted into a new row or used to update a row. (Such a SET statement has no effect in an AFTER trigger because the row change will have already occurred.)

您可能应该构建表以使 auto_increment 正常工作。更好的解决方案，可以在多个 session 同时插入数据库时​​发挥作用。