mysql 触发器存储触发器已被调用存储触发器的语句使用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1582683/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
mysql trigger stored trigger is already used by statement which invoked stored trigger
提问by Russ
I want to set up a trigger so that if on an update the prediction field is = 3 then the trigger changes the value to 4 and saves it in the database. The trigger is below.
我想设置一个触发器，以便如果在更新时预测字段为 = 3，那么触发器将值更改为 4 并将其保存在数据库中。触发器如下。
For some reason I keep getting an error saying:
出于某种原因，我不断收到错误消息：
#1442 - Can't update table 'tzanalytic\_forecast\_cached' in stored
function/trigger because it is already used by statement which invoked
this stored function/trigger.
Is this set up the right way?
这是正确的设置方式吗？
delimiter $$
CREATE TRIGGER no_BoW BEFORE UPDATE ON t FOR EACH ROW
BEGIN set @prediction = new.prediction;
UPDATE t SET t.prediction = (SELECT IF(@prediction = '3', '4', @prediction)) WHERE t.event_id = new.event_id AND t.price_tier = new.price_tier; END;
$$ delimiter ;
回答by Cory House
MySQL triggers can't manipulate the table they are assigned to. All other major DBMS support this feature so hopefully MySQL will add this support soon.
MySQL 触发器无法操作分配给它们的表。所有其他主要的 DBMS 都支持此功能，因此希望 MySQL 会尽快添加此支持。
http://forums.mysql.com/read.php?99,122354,240978#msg-240978
http://forums.mysql.com/read.php?99,122354,240978#msg-240978
回答by goonerify
Also, you need to ensure that you don't have other procedures or functions that perform updates on the table that this particular procedure is assigned to or you end up in a recursion. For instance
此外，您需要确保没有其他过程或函数对分配到该特定过程的表执行更新，或者您以递归结束。例如
create trigger trig1 After update on table1 FOR EACH ROW
BEGIN
UPDATE table2 SET colum1 = column1 + 1
END;
create trigger trig2 After update on table2 FOR EACH ROW
BEGIN
UPDATE table1 SET colum2 = column2 + 1
END;
This will end up in a recursion so beware of the existing stored procedures and functions.
这将以递归结束，因此请注意现有的存储过程和函数。
