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.