Józef Curyłło Józef Curyłło - 3 months ago 8x
MySQL Question

Update other rows in update trigger

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`
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;

However I got an error:

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.

As I think it just mean infinite loop. So do you know some way to make it correctly, except firing SQL function from PHP after update?


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.