mysql 触发器存储触发器已被调用存储触发器的语句使用

发布于 2024-08-07 21:09:23 字数 601 浏览 3 评论 0原文

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

由于某种原因，我不断收到错误消息：

#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 ;

原文

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.

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 ;

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

香草可樂 2024-08-14 21:09:23

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

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

回复收藏 0 原文

雪落纷纷 2024-08-14 21:09:23

此外，您还需要确保没有其他过程或函数对该特定过程分配到的表执行更新，否则您最终会陷入递归。例如，

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;

这将以递归结束，因此请注意现有的存储过程和函数。

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.

回复收藏 0 原文

~没有更多了~