mysql 错误码 存储表,MySql错误：无法更新存储函数/触发器中的表

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.

解决方案

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).