mysql after insert,mysql – 无法更新在AFTER INSERT中执行触发器的表

我正在运行MySQL 5.5.9和InnoDB.

我尝试创建一个版本化的表,其中当前字段指示记录是否是最新版本.就像是：

| autonumber | id | name | current

| 1 | 1 | Yes | 0

| 2 | 1 | No | 1

无论如何,我过去经常在MSSQL中通过一个AFTER INSERT触发器执行此操作,该触发器将具有相同id的所有记录更新为current = 0.所以这里我们进入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;

|

运行正常,但插入记录时：

insert into Products (id, name)

values (1, "Hello SO!");

我收到以下错误：

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.

有没有办法解决这个问题？

解决方法:

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

标签：mysql,triggers

来源： https://codeday.me/bug/20190626/1292040.html