我的 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

简单，这是一个体育锦标赛，当统计人员捕获比赛的分数时，他会自动添加一个played_game，并且分别是赢，平局或输球，所以我的触发器应该在他played_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.

此外，当我手动编辑 Played_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屋！