POSTGRESQL中修改MATCHES表数据时如何同步更新TEAMS表对应数据

先明确两张核心表的参考结构，你可以根据实际业务字段调整：

• matches表核心字段：match_id（主键）、home_team_id（主队ID，关联teams表）、away_team_id（客队ID，关联teams表）、home_score（主队得分）、away_score（客队得分）、status（比赛状态，比如completed表示已完赛，避免未结束的比赛提前更新积分）
• teams表核心字段：team_id（主键）、points（总积分）、wins（胜场数）、draws（平场数）、losses（负场数）等需要同步统计的字段

步骤1：创建触发器函数 #

触发器函数会在matches表符合触发条件时自动执行积分同步逻辑：

CREATE OR REPLACE FUNCTION update_team_points_after_match_update()
RETURNS TRIGGER AS $$
DECLARE
    home_point INT := 0;
    away_point INT := 0;
BEGIN
    -- 场景1：首次将比赛标记为已完赛，直接计算积分更新
    IF NEW.status = 'completed' AND OLD.status != 'completed' THEN
        -- 积分规则默认胜3、平1、负0，可根据联赛规则自行调整
        IF NEW.home_score > NEW.away_score THEN
            home_point = 3;
        ELSIF NEW.home_score = NEW.away_score THEN
            home_point = 1;
            away_point = 1;
        ELSE
            away_point = 3;
        END IF;

        -- 更新主队数据
        UPDATE teams
        SET points = points + home_point,
            wins = wins + CASE WHEN home_point = 3 THEN 1 ELSE 0 END,
            draws = draws + CASE WHEN home_point = 1 THEN 1 ELSE 0 END,
            losses = losses + CASE WHEN home_point = 0 THEN 1 ELSE 0 END
        WHERE team_id = NEW.home_team_id;

        -- 更新客队数据
        UPDATE teams
        SET points = points + away_point,
            wins = wins + CASE WHEN away_point = 3 THEN 1 ELSE 0 END,
            draws = draws + CASE WHEN away_point = 1 THEN 1 ELSE 0 END,
            losses = losses + CASE WHEN away_point = 0 THEN 1 ELSE 0 END
        WHERE team_id = NEW.away_team_id;
    
    -- 场景2：已完赛的比赛被修改比分，先回退旧积分再更新新积分
    ELSIF NEW.status = 'completed' AND OLD.status = 'completed' THEN
        -- 回退旧比分对应的积分
        IF OLD.home_score > OLD.away_score THEN
            home_point = 3;
        ELSIF OLD.home_score = OLD.away_score THEN
            home_point = 1;
            away_point = 1;
        ELSE
            away_point = 3;
        END IF;

        UPDATE teams
        SET points = points - home_point,
            wins = wins - CASE WHEN home_point = 3 THEN 1 ELSE 0 END,
            draws = draws - CASE WHEN home_point = 1 THEN 1 ELSE 0 END,
            losses = losses - CASE WHEN home_point = 0 THEN 1 ELSE 0 END
        WHERE team_id = OLD.home_team_id;

        UPDATE teams
        SET points = points - away_point,
            wins = wins - CASE WHEN away_point = 3 THEN 1 ELSE 0 END,
            draws = draws - CASE WHEN away_point = 1 THEN 1 ELSE 0 END,
            losses = losses - CASE WHEN away_point = 0 THEN 1 ELSE 0 END
        WHERE team_id = OLD.away_team_id;

        -- 计算新比分对应的积分并更新
        home_point = 0;
        away_point = 0;
        IF NEW.home_score > NEW.away_score THEN
            home_point = 3;
        ELSIF NEW.home_score = NEW.away_score THEN
            home_point = 1;
            away_point = 1;
        ELSE
            away_point = 3;
        END IF;

        UPDATE teams
        SET points = points + home_point,
            wins = wins + CASE WHEN home_point = 3 THEN 1 ELSE 0 END,
            draws = draws + CASE WHEN home_point = 1 THEN 1 ELSE 0 END,
            losses = losses + CASE WHEN home_point = 0 THEN 1 ELSE 0 END
        WHERE team_id = NEW.home_team_id;

        UPDATE teams
        SET points = points + away_point,
            wins = wins + CASE WHEN away_point = 3 THEN 1 ELSE 0 END,
            draws = draws + CASE WHEN away_point = 1 THEN 1 ELSE 0 END,
            losses = losses + CASE WHEN away_point = 0 THEN 1 ELSE 0 END
        WHERE team_id = NEW.away_team_id;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

步骤2：绑定触发器到matches表 #

触发器仅在比分、比赛状态字段更新时触发，避免无效运算：

CREATE TRIGGER trigger_update_team_points
AFTER UPDATE OF home_score, away_score, status ON matches
FOR EACH ROW
EXECUTE FUNCTION update_team_points_after_match_update();

补充说明 #

• 逻辑已经覆盖常规业务场景，如果有新增完赛比赛、删除完赛比赛的需求，也可以参照上述逻辑编写对应的AFTER INSERT、AFTER DELETE触发器适配
• 所有积分计算规则都可以根据你的联赛规则直接修改函数内的赋值逻辑即可

内容的提问来源于stack exchange，提问作者OscarNava84