mysql 触发器存储触发器已被调用存储触发器的语句使用 [英] mysql trigger stored trigger is already used by statement which invoked stored trigger

问题描述

我想设置一个触发器，以便如果在更新时预测字段 = 3，那么触发器将值更改为 4 并将其保存在数据库中.触发器如下.

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.

出于某种原因，我不断收到错误消息:

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.

这样设置正确吗?

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 ;

推荐答案

MySQL 触发器无法操作分配给它们的表.所有其他主要的 DBMS 都支持此功能，因此希望 MySQL 会尽快添加此支持.

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.

http://forums.mysql.com/read.php?99,122354,240978#msg-240978

这篇关于mysql 触发器存储触发器已被调用存储触发器的语句使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！