Oracle SQL中如何限制表内同一个GROUP_ID值的出现次数不超过N？

ORA-04091变异表错误的核心原因是行级触发器触发时，students表正处于变更未提交状态，Oracle为了保证读一致性，禁止在此阶段直接查询该表。以下是两种可落地的实现方案：

方案一：分组表冗余计数 + 约束校验（推荐） #

该方案性能最优，且支持灵活配置每个分组的人数上限，完全规避变异表问题。

1. 首先修改groups表，新增组内学生计数和人数上限字段：

ALTER TABLE "groups" ADD (
  "STUDENT_COUNT" NUMBER(6,0) DEFAULT 0 NOT NULL,
  "MAX_STUDENTS" NUMBER(6,0) DEFAULT 10 NOT NULL -- 可单独调整每个分组的人数上限，默认10
);

2. 为students表创建行级触发器，实时更新对应分组的学生计数：

CREATE OR REPLACE TRIGGER trg_students_group_count
AFTER INSERT OR UPDATE OF GROUP_ID OR DELETE ON "students"
FOR EACH ROW
BEGIN
  -- 旧分组计数扣减（删除、调整学生分组场景）
  IF DELETING OR UPDATING THEN
    UPDATE "groups"
    SET STUDENT_COUNT = STUDENT_COUNT - 1
    WHERE GROUP_ID = :OLD.GROUP_ID;
  END IF;
  -- 新分组计数增加（新增学生、调整学生分组场景）
  IF INSERTING OR UPDATING THEN
    UPDATE "groups"
    SET STUDENT_COUNT = STUDENT_COUNT + 1
    WHERE GROUP_ID = :NEW.GROUP_ID;
  END IF;
END;
/

3. 为groups表增加校验约束，直接限制组内人数不超过上限：

ALTER TABLE "groups" ADD CONSTRAINT chk_group_size
CHECK (STUDENT_COUNT <= MAX_STUDENTS);

方案优势：

• 无变异表问题，支持所有增删改场景
• 性能远高于每次统计学生表行数，仅需修改分组表单行字段
• 支持不同分组设置不同的人数上限，调整时仅需修改MAX_STUDENTS字段值
  注意：如果students表已有存量数据，需要先手动统计每个分组的学生数，回填到STUDENT_COUNT字段后再开启约束。

方案二：复合触发器（无需修改表结构） #

如果使用Oracle 11g及以上版本，且不想修改现有表结构，可以使用复合触发器，把计数校验逻辑放到语句执行完成后的阶段执行，规避变异表限制：

CREATE OR REPLACE TRIGGER trg_check_group_size
FOR INSERT OR UPDATE OF GROUP_ID ON "students"
COMPOUND TRIGGER
  TYPE t_group_ids IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  changed_groups t_group_ids;

BEFORE EACH ROW IS
BEGIN
  -- 收集所有变更涉及的分组ID，避免重复校验
  changed_groups(:NEW.GROUP_ID) := :NEW.GROUP_ID;
  IF UPDATING THEN
    changed_groups(:OLD.GROUP_ID) := :OLD.GROUP_ID;
  END IF;
END BEFORE EACH ROW;

AFTER STATEMENT IS
  student_count NUMBER;
BEGIN
  -- 遍历所有涉及的分组，统一校验人数
  FOR i IN 1..changed_groups.COUNT LOOP
    SELECT COUNT(*) INTO student_count
    FROM "students"
    WHERE GROUP_ID = changed_groups(i);
    -- 如需灵活调整上限，可替换为读取配置表的对应值
    IF student_count > 10 THEN
      raise_application_error(-20001, '分组'||changed_groups(i)||'学生人数超过上限10人');
    END IF;
  END LOOP;
END AFTER STATEMENT;
END trg_check_group_size;
/

方案优势：无需修改现有表结构，缺点是分组数据量大时，count统计的性能低于方案一。

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