Question 235134 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Do not update another field with trigger after update

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

Tags:

2

Answers


  1. 0

    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. 0

    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