如何解决触发器变异问题，实现课程排期的重复插入拦截

我需要阻止向排期表插入重复记录：当新课程的开始日期处于已有同地点课程的起止日期范围内时，不允许插入。

我编写了一个触发器，能编译但存在触发器变异问题。尝试用复合触发器解决，但要么实现不了，要么理解有误，始终没成功——我原本以为复合触发器应该在before statement阶段处理，但尝试后只得到错误。

我也考虑过after insert/update触发器，但这是在记录插入后生效，感觉不合适，而且我认为同样会有变异问题。

我编写的触发器代码如下：

CREATE OR REPLACE TRIGGER PREVENT_INSERTS 
before insert or update on tbl_classes

    DECLARE
        v_count number;
        v_start TBL_CLASS_SCHED.start_date%type;
        v_end TBL_CLASS_SCHED.end_date%type;
        v_half TBL_CLASS_SCHED.day_is_half%type;
    BEGIN
        select start_date, end_date, day_is_half
        into v_start, v_end, v_half
        from tbl_classes 
        where class_id = :NEW.CLASS_ID
        and location_id = :NEW.location_id;

        select count(*) 
        into v_count 
        from TBL_CLASS_SCHED
        where :NEW.START_DATE >= (select start_date 
                                   from TBL_CLASS_SCHED 
                                   where class_id = :NEW.CLASS_ID
                                   and location_id = :NEW.location_id)
        and :NEW.START_DATE <= (select end_date
                                   from TBL_CLASS_SCHED
                                   where class_id = :NEW.CLASS_ID
                                   and location_id = :NEW.location_id);

        if (v_count = 2) THEN
            RAISE_APPLICATION_ERROR(-20001,'You cannot schedule more than 2 classes that are a half day at the same location');
        end if;
        if (v_count = 1 and :NEW.day_is_half = 1) THEN
            if (v_half != 1) THEN
                RAISE_APPLICATION_ERROR(-20001,'You cannot schedule a class during another class''s time period of the same type at the same location');
            end if;
        end if;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            null;
    END;
    
end PREVENT_INSERTS ;

目前我已经在插入或更新前直接用相同逻辑实现了，但希望把它做成约束/触发器来确保规则始终生效，同时学习相关技术。

1. 原触发器问题分析 #

你的触发器有两个核心问题：

• 变异表问题：行级触发器中直接查询触发表tbl_classes，Oracle不允许这种操作——触发表处于中间状态，数据尚未最终确定，直接查询会引发变异表错误。
• 逻辑混乱：嵌套查询TBL_CLASS_SCHED的条件没有正确匹配"同地点课程时间重叠"的核心规则，逻辑绕弯且容易出错。

2. 复合触发器解决变异表问题 #

复合触发器可以在不同阶段（语句级、行级）分离数据收集与规则校验，避免直接查询触发表的问题。以下是实现代码：

CREATE OR REPLACE TRIGGER PREVENT_DUPLICATE_CLASSES
FOR INSERT OR UPDATE ON tbl_classes
COMPOUND TRIGGER
    -- 定义集合存储待校验的记录
    TYPE class_rec IS RECORD (
        class_id tbl_classes.class_id%TYPE,
        location_id tbl_classes.location_id%TYPE,
        start_date tbl_classes.start_date%TYPE,
        end_date tbl_classes.end_date%TYPE,
        day_is_half tbl_classes.day_is_half%TYPE
    );
    TYPE class_tab IS TABLE OF class_rec;
    v_classes class_tab := class_tab();

    -- BEFORE STATEMENT阶段：初始化集合
    BEFORE STATEMENT IS
    BEGIN
        v_classes.delete;
    END BEFORE STATEMENT;

    -- BEFORE EACH ROW阶段：收集当前待处理的行数据
    BEFORE EACH ROW IS
    BEGIN
        v_classes.extend;
        v_classes(v_classes.last).class_id := :NEW.class_id;
        v_classes(v_classes.last).location_id := :NEW.location_id;
        v_classes(v_classes.last).start_date := :NEW.start_date;
        v_classes(v_classes.last).end_date := :NEW.end_date;
        v_classes(v_classes.last).day_is_half := :NEW.day_is_half;
    END BEFORE EACH ROW;

    -- AFTER STATEMENT阶段：统一校验所有待处理记录的规则
    AFTER STATEMENT IS
        v_conflict_count NUMBER;
    BEGIN
        FOR i IN v_classes.first .. v_classes.last LOOP
            -- 核心规则：同地点下，当前课程时间是否与已有课程重叠
            SELECT COUNT(*)
            INTO v_conflict_count
            FROM tbl_classes c
            WHERE c.location_id = v_classes(i).location_id
              AND c.class_id != v_classes(i).class_id -- 排除当前记录（更新场景）
              AND (
                  v_classes(i).start_date BETWEEN c.start_date AND c.end_date
                  OR c.start_date BETWEEN v_classes(i).start_date AND v_classes(i).end_date
              );

            IF v_conflict_count > 0 THEN
                RAISE_APPLICATION_ERROR(-20001, '无法操作：同地点已有课程与当前课程时间重叠');
            END IF;

            -- 半天课程数量限制规则
            SELECT COUNT(*)
            INTO v_conflict_count
            FROM tbl_classes c
            WHERE c.location_id = v_classes(i).location_id
              AND c.day_is_half = 1;

            IF v_conflict_count > 2 THEN
                RAISE_APPLICATION_ERROR(-20002, '同地点的半天课程数量不能超过2个');
            END IF;

            -- 半天与全天课程冲突规则
            SELECT COUNT(*)
            INTO v_conflict_count
            FROM tbl_classes c
            WHERE c.location_id = v_classes(i).location_id
              AND c.day_is_half != v_classes(i).day_is_half
              AND (
                  v_classes(i).start_date BETWEEN c.start_date AND c.end_date
                  OR c.start_date BETWEEN v_classes(i).start_date AND v_classes(i).end_date
              );

            IF v_conflict_count > 0 AND v_classes(i).day_is_half = 1 THEN
                RAISE_APPLICATION_ERROR(-20003, '无法插入半天课程：同地点已有全天课程时间重叠');
            END IF;
        END LOOP;
    END AFTER STATEMENT;
END PREVENT_DUPLICATE_CLASSES;
/

3. 替代方案：物化视图+原生约束 #

如果触发器维护成本高，可以用物化视图将冲突逻辑转化为数据库原生约束，稳定性更高：

-- 创建物化视图日志（支持快速刷新）
CREATE MATERIALIZED VIEW LOG ON tbl_classes
WITH ROWID, (class_id, location_id, start_date, end_date, day_is_half)
INCLUDING NEW VALUES;

-- 创建物化视图，检测同地点时间重叠的课程
CREATE MATERIALIZED VIEW mv_class_conflicts
REFRESH FAST ON COMMIT
AS
SELECT c1.location_id, c1.rowid AS rowid1, c2.rowid AS rowid2
FROM tbl_classes c1
JOIN tbl_classes c2
  ON c1.location_id = c2.location_id
  AND c1.class_id != c2.class_id
  AND (
      c1.start_date BETWEEN c2.start_date AND c2.end_date
      OR c2.start_date BETWEEN c1.start_date AND c1.end_date
  );

-- 添加唯一约束，禁止出现冲突记录
ALTER TABLE mv_class_conflicts ADD CONSTRAINT mv_no_overlap UNIQUE (location_id, rowid1, rowid2);

4. 注意事项 #

• 复合触发器要处理批量插入/更新场景，确保所有待处理记录都被校验
• 物化视图方案需考虑数据刷新性能，适合数据量中等的场景
• 两种方案都要在测试环境验证并发操作、批量操作下的规则有效性

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