无法更新在 AFTER INSERT 中执行触发器的表 [英] Can't update table that trigger is executed on in AFTER INSERT

问题描述

我正在运行 MySQL 5.5.9 和 InnoDB.

I'm running MySQL 5.5.9 and InnoDB.

我尝试创建一个版本化表，其中 current 字段指示记录是否是最新版本.类似的东西:

I try to create a versioned table, where the current field indicates whether a record is the most recent version. Something like:

| autonumber | id | name | current
| 1          | 1  | Yes  | 0
| 2          | 1  | No   | 1

无论如何，我过去经常通过 AFTER INSERT 触发器在 MSSQL 中执行此操作，该触发器将具有相同 ID 的所有记录更新为 current = 0.所以我们在 MySQL 中进行:

Anyhow, I did this in the past in MSSQL quite often via an AFTER INSERT trigger that updates all records with the same id to current = 0. So here we go in MySQL:

DELIMITER |

CREATE TRIGGER TRIGGER_Products_UpdateEarlierVersions AFTER INSERT ON Products
FOR EACH ROW BEGIN
    UPDATE Products
    SET current = 0
    WHERE   id = new.id
        AND current = 1
        AND autonumber <> new.autonumber;
END;
|

这运行良好，但插入记录时:

This runs fine, but when inserting a record:

insert into Products (id, name)
values (1, "Hello SO!");

我收到以下错误:

错误代码:1442.无法更新存储函数/触发器中的表产品"，因为它已被调用此存储函数/触发器的语句使用.

Error Code: 1442. Can't update table 'Products' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

有没有办法解决这个问题来实现类似的事情?

Is there a way around this to achieve a similar thing?

推荐答案

摘自这里 http://forums.mysql.com/read.php?99,122354,122505#msg-122505

当你插入一条记录时，mysql 正在做一些锁定的事情.你不能插入/更新/删除您插入的同一表的行..因为然后触发器会一次又一次地调用..最终递归

when you insert a record mysql is doing some lock stuff. you can't insert/update/delete rows of the same table where you insert.. because then the trigger would called again and again.. ending up in a recursion

这篇关于无法更新在 AFTER INSERT 中执行触发器的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！