我正在运行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.

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

解决方法:

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

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