MySQL: Cannot update table 'order' in stored function/trigger because it is already used by a statement that calls this stored function/trigger

Question

I have a table named orders in a MySQL database. The payment_date property is Null until we receive the funds, at which time it is updated to the date. Once the payment_date attribute is updated, the order_state attribute is updated (manually!) 1 or 2 to the value 3.

I want to create a trigger to automate this process. Here's my attempt:

DELIMITER $$

CREATE TRIGGER update_order_state

AFTER UPDATE
ON orders FOR EACH ROW
BEGIN
    IF ( NEW.payment_date IS NOT NULL AND NEW.order_state IN (1, 2) )
        THEN
        UPDATE orders SET order_state = 3 WHERE NEW.payment_date IS NOT NULL and NEW.order_state IN (1, 2);
    END IF;
END $$

DELIMITER ;

When I call it, I get the following error message:

[HY000][1442] The table 'orders' in the stored function/trigger cannot be updated because it is already used by a statement that calls this stored function/trigger.

Considering that this might be a locking situation (or risk of an infinite loop), I changed the trigger to BEFORE UPDATE. However, I received the same error message.

How can I solve this problem?

Thanks!

Answer 1

Ok, given that you commented above that you only need to update the row that generated the trigger, you can do this without using UPDATE in the trigger body.

DELIMITER $$

CREATE TRIGGER update_order_state
BEFORE UPDATE
ON orders FOR EACH ROW
BEGIN
    IF ( NEW.payment_date IS NOT NULL AND NEW.order_state IN (1, 2) )
        THEN
        SET NEW.order_state = 3;
    END IF;
END $$

DELIMITER ;

SettingsNEW.Applies only to the corresponding row where the trigger was generated.

The trigger body processes one row at a time, as indicated by FOR EACH ROW. Therefore, if the UPDATE operation that generates this trigger involves multiple rows, one row will be processed at a time.

Changing any value of NEW. requires the use of a BEFORE trigger. Once the AFTER trigger has run, it is too late to change any values.