skip to Main Content

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

2

Answers


  1. 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 ;
    
    Login or Signup to reply.
  2. 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

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search