MySql错误:无法更新存储的函数/触发器中的表 [英] MySql Error: Can't update table in stored function/trigger

问题描述

我正在使用MySQL和Engine InnoDB.如图所示，我有一个包含4列的SQL表(简体):

当需求的状态变为完成"时，我希望其优先级为空，并且所有具有上述优先级的需求都将递减.

例如，如果第二个需求:面包"为完成"，则其优先级将设置为空，并且我希望黄油"的优先级为2，果酱"的优先级为3.

我有这个触发器:

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 ;

但是当我将状态从进行中"编辑为完成"的行时出现错误.

#1442-无法更新存储的函数/触发器中的表需求"，因为调用该存储的语句已使用该表 功能/触发.

我已经在Internet上寻找了此错误，但这是我第一次使用SQL触发器，所以我没有成功解决问题. 谢谢您的帮助.

解决方案

当您尝试更新表时，您会在触发器处启动一个无穷循环，触发器不用于更新自己的表，您可以创建一个新表，您可以在其中保存表的主键值和优先级值，以便可以在该表上更新优先级.在您的应用程序中或您将要使用的任何地方，您都可以将这两个表连接起来.您也可以考虑在应用程序端使用以下sql更新表:

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

还可以编写一个新的存储过程来每隔5/10分钟(无论需要什么)来修复您的数据向导.

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

这篇关于MySql错误:无法更新存储的函数/触发器中的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！