解决PL/SQL复合触发器AFTER STATEMENT中无法使用:OLD/:NEW的问题

表结构与前置信息 #

Battles表（主键：Name） #

Consequences表（唯一键：(Ship, Battle)，外键：Battle关联Battles.Name） #

需求 #

当舰船参与了当前战斗之后发生的其他战斗时，禁止将该舰船在当前战斗中的Result列更新为'destroyed'。例如：无法修改(ShipX, Battle1)的Result为'destroyed'，因为ShipX还参与了晚于Battle1的Battle2。

现有问题 #

已编写无语法错误的check_date_func函数，但在创建复合触发器update_result_trigg时，遇到AFTER STATEMENT段无法使用:OLD和:NEW伪记录的问题，导致校验逻辑无法执行。

修复方案 #

1. 修正校验函数逻辑 #

原函数存在循环提前返回、变量拼写错误等逻辑问题，替换为更高效简洁的实现：

CREATE OR REPLACE FUNCTION check_date_func (p_ship IN VARCHAR, p_battle IN VARCHAR) RETURN BOOLEAN IS
  v_current_battle_date DATE;
BEGIN
  -- 获取当前战斗的日期
  SELECT b.b_date INTO v_current_battle_date
  FROM Battles b
  JOIN Consequences c ON b.Name = c.Battle
  WHERE c.Ship = p_ship AND c.Battle = p_battle;

  -- 检查该舰船是否参与了当前战斗之后的其他战斗
  RETURN EXISTS (
    SELECT 1
    FROM Consequences c_later
    JOIN Battles b_later ON c_later.Battle = b_later.Name
    WHERE c_later.Ship = p_ship
      AND b_later.B_date > v_current_battle_date
  );
END check_date_func;

2. 调整复合触发器的逻辑结构 #

利用复合触发器的分段特性，在BEFORE EACH ROW段收集需要校验的记录，在AFTER STATEMENT段执行校验，避开:OLD/:NEW的使用限制：

CREATE OR REPLACE TRIGGER update_result_trigg
FOR UPDATE OF Result ON Consequences
COMPOUND TRIGGER
  -- 定义集合存储待校验的舰船-战斗对
  TYPE t_ship_battle IS RECORD (
    ship VARCHAR2(50),
    battle VARCHAR2(50)
  );
  TYPE t_ship_battle_tab IS TABLE OF t_ship_battle INDEX BY BINARY_INTEGER;
  g_ship_battles t_ship_battle_tab;
  g_index BINARY_INTEGER := 0;

  -- BEFORE EACH ROW：收集需要校验的更新操作
  BEFORE EACH ROW IS
  BEGIN
    -- 仅当更新后Result为'destroyed'时才收集记录
    IF :NEW.Result = 'destroyed' THEN
      g_index := g_index + 1;
      g_ship_battles(g_index).ship := :OLD.Ship;
      g_ship_battles(g_index).battle := :OLD.Battle;
    END IF;
  END BEFORE EACH ROW;

  -- AFTER STATEMENT：遍历集合执行校验
  AFTER STATEMENT IS
  BEGIN
    FOR i IN 1..g_index LOOP
      -- 调用校验函数，若存在后续战斗则抛出错误
      IF check_date_func(g_ship_battles(i).ship, g_ship_battles(i).battle) THEN
        RAISE_APPLICATION_ERROR(-20001, 
          '无法将舰船' || g_ship_battles(i).ship || '在战斗' || g_ship_battles(i).battle || '的结果设为destroyed：该舰船参与了后续战斗'
        );
      END IF;
    END LOOP;
  END AFTER STATEMENT;
END update_result_trigg;

方案说明 #

• BEFORE EACH ROW段：拦截所有将Result更新为'destroyed'的操作，把对应的Ship和Battle存入全局集合，此时可以正常使用:OLD获取原始记录信息。
• AFTER STATEMENT段：遍历集合，对每个待校验记录调用函数，若发现存在后续战斗则抛出自定义错误，回滚整个更新语句。
• 修正后的校验函数使用EXISTS子查询替代游标遍历，逻辑更清晰，执行效率更高。

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