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
You can not update the same row that invoked the trigger in an
AFTER
Trigger, you need to change it in aBEFORE TRIGGER
So you would
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
and you can see the same question with link