MySQL 更新时触发总和

Question

我正在尝试使用 MySQL 中的触发器来获取单元格 Points_Per_Game 进行计算，但不确定我错过了什么。

Points_Per_Game = Total_Points / Games Played, 


DELIMITER $$
CREATE TRIGGER trg_Point_Per_Game 
AFTER UPDATE ON Players 
FOR EACH ROW 
BEGIN 
UPDATE Players SET Points_Per_Game = Total_Points / Games_Played;  
END;$$
DELIMITER;

玩家表:

+------------+--------------+--------------+-----------------+
| Name       | Games_Played | Total_Points | Points_Per_Game |
+------------+--------------+--------------+-----------------+
| David      |            0 |            0 |            NULL |
| John       |            6 |            7 |            NULL |

Games_Played 和 Total_Points 列通过 bash 脚本每周更新一次。

Answer 1

您想要一个before更新触发器，并且需要引用该行中的数据:

DELIMITER $$
CREATE TRIGGER trg_Point_Per_Game 
BEFORE UPDATE ON Players 
FOR EACH ROW 
BEGIN 
    SET new.Points_Per_Game = (CASE WHEN new.Games_Played > 0 THEN new.Total_Points / new.Games_Played ELSE 0 END);  
END;$$
DELIMITER;