I want to update order_key in some rows after update. Generally this is my idea to make it:
CREATE TRIGGER `trigger_numbers_before_update` BEFORE UPDATE ON `numbers`
FOR EACH ROW BEGIN
IF NEW.order_key <> OLD.order_key THEN
UPDATE `numbers` set `order_key` = `order_key`+1 where `order_key` >= NEW.order_key AND `book_id`=NEW.book_id;
Error: SQLSTATE[HY000]: General error: 1442 Can't update table 'numbers' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
No. It's not an infinite loop.
It's a MySQL restriction on what operations can be performed within a trigger.
There is no way to perform this type of UPDATE operation (on rows in
numbers) from within an UPDATE trigger on
numbers. And you can't move the UPDATE statement to a function, and then call the function from the trigger... that UPDATE cannot be performed with the execution context of the trigger.
To have this update performed, the UPDATE would need to be performed outside of the execution context of a trigger.
And not addressed in this answer is the question of why you would want to perform such an operation in the first place.