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

I’d like to create a trigger to automate this flow. 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 invoke this, I get the following error message:

[HY000][1442] Can't update table 'orders' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

Thinking that this could be a case of locking (or risk of an infinite loop), I changed the trigger to BEFORE UPDATE. But, I get the same error message.

How would I work around this?

Thanks!

>Solution :

Okay, given you commented above that you only need to update the rows that spawned the trigger, you can do that without using UPDATE in the body of the trigger.

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 ;

Setting NEW.<column> only applies to the respective row that spawned the trigger.

The trigger body processes one row at a time, indicated by FOR EACH ROW. So if the UPDATE action that spawned this trigger involves multiple rows, they will each be processed one at a time.

Changing any value of NEW.<column> requires that you use a BEFORE trigger. Once the AFTER trigger runs, it’s too late to change any values.