Error while updating a row in my SQL which has a set trigger

Question

In the error console it displays :

Error Code: 1442. Can't update table 'warriors' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

This is my table :

This is the trigger creation statement:

create trigger power_update
after update on   warriors 
 for each row update warriors 
set power = (practise_hours * 100);

This is the statement to update the table

update warriors set practise_hours = 80 where id = 9;

Your update trigger updates the same table. This would lead to an infinite loop, in which the trigger would cause itself to fire. Hence it gets rejected. — Jonathan Willcock, May 27 '23 at 13:22

score 1 · Answer 1 · answered May 27 '23 at 15:20

This should Work Fine in MySQL

DELIMITER //

CREATE TRIGGER update_power BEFORE UPDATE ON warriors
FOR EACH ROW
BEGIN
  SET NEW.powers = NEW.practice_hours * 100;
END //

DELIMITER ;

You have to use the NEW keyword to reference the updated values combined with a BEFORE trigger since the AFTER cannot modify the NEW row values.

DELIMITER allows us to change the default delimiter ;

However, a Generated/Computed Column might be a better solution.

Error while updating a row in my SQL which has a set trigger

1 Answers1