Do not update another field with trigger after update

Question

I create one trigger from Mysql and my targer is when has update value of filed extpoint2 trigger will update value of filed left_coin and set value of this to: left_coin + ( new.extpoint2 - old.extpoint2 );

and this is my trigger

DELIMITER //
CREATE TRIGGER update_left_coin AFTER UPDATE ON account_zone
FOR EACH ROW
BEGIN
   IF !(NEW.extpoint_2 <=> OLD.extpoint_2) THEN
      UPDATE account_zone SET account_zone.left_coin = case WHEN NEW.extpoint_2 then account_zone.left_coin ELSE account_zone.left_coin + (NEW.extpoint_2 - OLD.extpoint_2) END;
   END IF;
END;//
DELIMITER ;

but when I update value for filed **extpoint2 ** this return error massage:

1442 - Can't update table 'account_zone' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. How to fix it

score 0 · Answer 1 · answered Aug 20 '23 at 20:35

You can not update the same row that invoked the trigger in an AFTER Trigger, you need to change it in a BEFORE TRIGGER

So you would

DELIMITER //
CREATE TRIGGER update_left_coin BEFORE UPDATE ON account_zone
FOR EACH ROW
BEGIN
   IF !(NEW.extpoint_2 <=> OLD.extpoint_2) THEN
      NEW.left_coin = case WHEN NEW.extpoint_2 then NEW.left_coin ELSE NEW.left_coin + (NEW.extpoint_2 - OLD.extpoint_2) END;
   END IF;
END;//
DELIMITER ;

score 0 · Accepted Answer · answered Aug 21 '23 at 21:29

You cannot change a table while the UPDATE trigger is firing

However, depending on what you're trying to achieve, you can access the new values by using NEW.left_coin or even the old values --if doing an UPDATE-- with OLD.

So you would

DELIMITER //
CREATE TRIGGER update_left_coin
BEFORE UPDATE ON account_zone
FOR EACH ROW
BEGIN
    IF NEW.extpoint_2 <> OLD.extpoint_2 THEN
        SET NEW.left_coin = OLD.left_coin + (NEW.extpoint_2 - OLD.extpoint_2);
    END IF;
END;
//DELIMITER ;

and you can see the same question with link

Do not update another field with trigger after update

2 Answers2