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!
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
Ok, given that you commented above that you only need to update the row that generated the trigger, you can do this without using
UPDATEin 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 ;Settings
NEW.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 theUPDATEoperation that generates this trigger involves multiple rows, one row will be processed at a time.Changing any value of
NEW.requires the use of aBEFOREtrigger. Once theAFTERtrigger has run, it is too late to change any values.