0

I need to update the parent item's updated_at field when any of it's child item's updated_at field is updated.

DELIMITER $$
CREATE TRIGGER after_item_update
AFTER UPDATE
ON item FOR EACH ROW
BEGIN
    IF OLD.updated_at <> NEW.updated_at AND OLD.parent_id > 0 THEN
        UPDATE item i set updated_at = now() where i.id = OLD.parent_id;
    END IF;
END$$
DELIMITER ;

I tried above query but it gives me following error when I try to update any row from the table.

ERROR 1442 (HY000): Can't update table 'item' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.```
Xylon Gracias
  • 43
  • 5
  • Did you try BEFORE UPDATE ? https://stackoverflow.com/questions/4547465/updating-table-in-trigger-after-update-on-the-same-table – ikiK Aug 06 '20 at 09:28
  • @ikiK yes, I tried, still I get the same error. – Xylon Gracias Aug 06 '20 at 09:35
  • 1
    You cannot do what you want with a trigger. Consider 2 updates in a transaction or updating using a procedure or periodically updating parent using an event. – P.Salmon Aug 06 '20 at 09:42

0 Answers0