Can't update table that trigger is executed on in AFTER INSERT

Question

I'm running MySQL 5.5.9 and InnoDB.

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

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!");

I get the following error:

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?

Answer 1

Taken from here 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