CompoundTrigger编译无报错但未更新球员出场时长问题排查

• 两张核心表：
  • CONVOCATORIA：字段包含Hour、DataJogo、EquipaID、NIF、MinutesPlayed，球员默认出场时长为90分钟
  • ACOES：字段包含Hour、DataJogo、EquipaID、NIF、Minute、TipoAcaoID
• 需求：球员吃到红牌（TipoAcaoID=8）或累计两张黄牌（TipoAcaoID=7）时，将CONVOCATORIA表中该球员的MinutesPlayed更新为处罚发生的Minute值
• 故障：最初使用普通触发器触发突变错误，改用复合触发器后编译通过，但未实际修改目标值，推测SELECT COUNT(*)未获取到正确数值

1. 全表统计而非针对当前球员和场次：原触发器在BEFORE STATEMENT阶段统计所有黄牌/红牌总数，并非当前操作对应的球员和比赛，统计值完全不符合需求
2. 更新语句无精准匹配条件：原UPDATE未限定EquipaID、DataJogo、Hour、NIF，会误更新多条记录，甚至可能因子查询返回多行报错
3. 未包含当前操作的记录：BEFORE STATEMENT读取的是触发前的旧数据，未计入正在插入/更新的这条动作，导致统计数量遗漏

CREATE OR REPLACE TRIGGER check_if_red_card_or_two_yellows
FOR INSERT OR UPDATE ON ACOES
COMPOUND TRIGGER

    v_yellow_count INT;
    v_red_count INT;
    v_penalty_minute INT;

BEFORE EACH ROW IS
BEGIN
    -- 统计当前球员本场比赛的黄牌总数（含当前操作的记录）
    SELECT COUNT(*)
    INTO v_yellow_count
    FROM ACOES
    WHERE NIF = :NEW.NIF
      AND DataJogo = :NEW.DataJogo
      AND Hour = :NEW.Hour
      AND TipoAcaoID = 7
      OR ( :NEW.TipoAcaoID = 7 AND NIF = :NEW.NIF AND DataJogo = :NEW.DataJogo AND Hour = :NEW.Hour );

    -- 统计当前球员本场比赛的红牌总数（含当前操作的记录）
    SELECT COUNT(*)
    INTO v_red_count
    FROM ACOES
    WHERE NIF = :NEW.NIF
      AND DataJogo = :NEW.DataJogo
      AND Hour = :NEW.Hour
      AND TipoAcaoID = 8
      OR ( :NEW.TipoAcaoID = 8 AND NIF = :NEW.NIF AND DataJogo = :NEW.DataJogo AND Hour = :NEW.Hour );

    -- 处理红牌情况：取最晚红牌的分钟更新出场时长
    IF v_red_count >= 1 THEN
        SELECT MAX(Minute)
        INTO v_penalty_minute
        FROM ACOES
        WHERE NIF = :NEW.NIF
          AND DataJogo = :NEW.DataJogo
          AND Hour = :NEW.Hour
          AND TipoAcaoID = 8
          OR ( :NEW.TipoAcaoID = 8 AND Minute = :NEW.Minute );

        UPDATE CONVOCATORIA
        SET MinutesPlayed = v_penalty_minute
        WHERE NIF = :NEW.NIF
          AND DataJogo = :NEW.DataJogo
          AND Hour = :NEW.Hour
          AND EquipaID = :NEW.EquipaID;
    -- 处理两张黄牌情况：取最后一张黄牌的分钟更新出场时长
    ELSIF v_yellow_count >= 2 THEN
        SELECT MINUTE
        INTO v_penalty_minute
        FROM (
            SELECT Minute, ROW_NUMBER() OVER (ORDER BY Minute DESC) AS rn
            FROM ACOES
            WHERE NIF = :NEW.NIF
              AND DataJogo = :NEW.DataJogo
              AND Hour = :NEW.Hour
              AND TipoAcaoID = 7
              OR ( :NEW.TipoAcaoID = 7 AND Minute = :NEW.Minute )
        )
        WHERE rn = 1;

        UPDATE CONVOCATORIA
        SET MinutesPlayed = v_penalty_minute
        WHERE NIF = :NEW.NIF
          AND DataJogo = :NEW.DataJogo
          AND Hour = :NEW.Hour
          AND EquipaID = :NEW.EquipaID;
    END IF;
END BEFORE EACH ROW;
END check_if_red_card_or_two_yellows;
/

• 精准定位目标：所有统计、更新操作都绑定NIF、DataJogo、Hour、EquipaID，仅处理当前操作对应的球员和比赛
• 包含当前操作记录：统计时加入正在插入/更新的动作，避免统计数量遗漏
• 避免误更新：UPDATE语句用完整条件匹配，不会修改无关数据
• 贴合实际规则：红牌取最晚发生时间，黄牌取累计到两张时的最后一张时间，符合比赛逻辑

内容的提问来源于stack exchange，提问作者D. Rodrigues