MySQL 错误 : Can't update table in stored function/trigger

Question

我正在使用 MySQL 和引擎 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 - Can't update table 'demand' in stored function/trigger because it is already used by statement which invoked this storedfunction/trigger.

我在网上找过这个错误，但这是我第一次使用SQL触发器，所以我没有成功纠正我的问题。感谢您的帮助。

Answer 1

当你试图更新你的表时，你在触发器处开始了一个无限循环，触发器不是为了更新它们自己的表，你可以创建一个新表，你可以在其中保存表的主键值和优先级值，以便您可以更新该表的优先级。在您的应用程序中或您将使用的任何地方，您都可以连接这两个表。您也可以考虑在应用程序端使用以下 sql 更新表:

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

还可以编写一个新的存储过程来修复您的数据每 5/10 分钟自动运行一次(无论需要什么)。