我的MySQL触发器不起作用，语法简单，不复杂 [英] My MySQL trigger doesn't work, simple syntax, not complicated

问题描述

我不知道为什么我的触发器不起作用，当我手动使用它时，查询将起作用，但是当我想由触发器进行更新时，它将不起作用.有人可以帮我知道为什么吗?

I don't know why my trigger isn't working, the query works when I use it manually, but when I want to be updated by a trigger it doesn't work. Can someone help me to know why?

这是我的触发器:

    CREATE TRIGGER `upd_PTS` AFTER UPDATE ON `pos_table`
 FOR EACH ROW BEGIN
    IF (NEW.played_games <> OLD.played_games)
    THEN  
        update pos_table set PTS=((NEW.won_games*2)+(NEW.tie_games*1));
    END IF;
END

简单，这是一场体育比赛，当统计人员获取比赛得分时，他会自动添加一个play_game并分别是获胜，平局或输掉的比赛，因此，当他的play_games更改且有变化时，我的触发器应自动执行更改"PTS"列.但这不起作用.

Simple, it's for a sport tournament, when the statistics person captures the score of the game, automatically he adds a played_game and it's respective won, tie or lost game, so my trigger should execute automatically when he played_games change and it's has to change the PTS column. But it isn't working.

另外，当我手动编辑playd_games列时，也会出现此错误: 无法更新存储函数/触发器中的表pos_table，因为调用该存储函数/触发器的语句已使用该表."

Also when I edit the played_games column manually I get this error: "Can't update table pos_table in stored function/trigger because it's already used by statement which invoked this stored function/trigger".

推荐答案

存储的函数或触发器无法修改已经存在的表 被调用该语句的语句所使用(用于读取或写入) 功能或触发器.

A stored function or trigger cannot modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger.

来自:存储的程序限制

通常使用在插入时触发的触发器，如果​​要更改要插入的值，请创建类型为BEFORE INSERT的触发器，然后更改NEW

Generally with a trigger that is fired on insert, if you want to change the value that is being inserted you make the trigger of the type BEFORE INSERT and change the values in NEW

还注意到以下语句不是您想要的.

also noticed that the following statement isn't what you want anyway.

update pos_table set PTS=((NEW.won_games*2)+(NEW.tie_games*1));

它会更新整个表，而我认为您只是在尝试更新一个单独的行.无论如何，这是一个简单的计算，因此您实际上不需要存储此列.您可以轻松地计算显示时的值，并使代码更简单+避免触发器问题.

It updates the entire table while I think you are only trying to update a sepcific row. Anyway, this is a simple calculation so you don't really need to store this column. You can just as easily calculate the value at display time and make your code a whole lot simpler + avoid the issue with the trigger.

这篇关于我的MySQL触发器不起作用，语法简单，不复杂的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！