MySql Error: Can't update table in stored function/trigger

Question

I am using MySQL and the Engine InnoDB. I have a SQL table (simplified) containing 4 columns as you can see in this picture :

When the state of a demand will become "Done" I want its Priority to be null and all demands with above priorities decremented.

For example, if the second demand : "Bread" is "Done", its priority will be set to null and I want "Butter" to have a priority of 2, and "Jam" a priority of 3.

I have this trigger :

DELIMITER |
CREATE TRIGGER modify_priority_trigger BEFORE UPDATE ON your_table
FOR EACH ROW 
begin
    if NEW.State= 'Done'
    then
       update your_table
       set priority = priority - 1
       where priority is not null
       and priority > NEW.priority;
    
       set NEW.priority = NULL;
    end if;
end
|
delimiter ;

But I have an error when I edit a line from a state from "In Progress" to "Done".

#1442 - Can't update table 'demand' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

I have looked for this error on the Internet but it's the first time I use SQL trigger so I haven't succeed to correct my problem. Thank's for your help.

Solution

At the moment when you are trying to update your table you start an endless loop at trigger, the triggers are not for updating their own tables, you can create a new table where you hold the value of your primary key of your table and priority value, so that you can update priority at that table. And in your application or where ever you will use, you can join these two tables. Also you may consider updating your table with the following sql in application side:

update your_table set state = 'NEW' and priority = priority - 1 where ....

Also a new stored procedure can be written to fix your data autorunning every 5/10 min (whatever desired).