Oracle创建默认地址触发器遇变异表等报错如何解决

错误原因说明 #

• 第一种行级触发器报错ORA-04091是Oracle的内置限制：行级触发器运行时，触发表正处于变更状态，不允许直接对该表执行DML操作，避免出现读一致性冲突，也就是你遇到的变异表错误。
• 第二种表级触发器报错ORA-04082是语法强制限制：:NEW/:OLD是行级变量，只能在行级触发器中引用，表级触发器没有行上下文，无法识别这类变量。

另外你原有逻辑还有一个隐藏错误：WHERE person_id = :NEW.id的条件写错了，:NEW.id是当前地址记录的主键，不是关联的用户ID，正确条件应该是person_id = :NEW.person_id，否则就算触发器能运行也无法匹配到同用户的其他地址。

正确实现（Oracle 11g及以上版本，首选复合触发器方案） #

复合触发器可以拆分触发的时间节点，既可以拿到行级的:NEW变量，又可以避开变异表限制，实现逻辑如下：

CREATE OR REPLACE TRIGGER trg_aiur_default_address
FOR UPDATE OF default_address ON address
COMPOUND TRIGGER
  -- 定义记录类型存储用户ID和被设为默认的地址ID
  TYPE r_addr_info IS RECORD (
    person_id address.person_id%TYPE,
    address_id address.id%TYPE
  );
  TYPE t_addr_infos IS TABLE OF r_addr_info INDEX BY PLS_INTEGER;
  g_addr_infos t_addr_infos;

BEFORE EACH ROW IS
BEGIN
  -- 仅当当前记录被设置为默认地址时才记录对应信息
  IF :NEW.default_address = 1 AND (:OLD.default_address IS NULL OR :OLD.default_address != 1) THEN
    g_addr_infos(g_addr_infos.COUNT + 1).person_id := :NEW.person_id;
    g_addr_infos(g_addr_infos.COUNT).address_id := :NEW.id;
  END IF;
END BEFORE EACH ROW;

AFTER STATEMENT IS
BEGIN
  -- 语句执行完成后统一更新同用户其他地址的默认标识
  FOR i IN 1 .. g_addr_infos.COUNT LOOP
    UPDATE address
    SET default_address = 0
    WHERE person_id = g_addr_infos(i).person_id
      AND id != g_addr_infos(i).address_id
      AND default_address = 1;
  END LOOP;
END AFTER STATEMENT;
END trg_aiur_default_address;
/

如果需要支持插入新地址时直接设为默认、同时清空该用户原有默认地址的场景，只需要把触发器的声明行改成FOR INSERT OR UPDATE OF default_address ON address即可，不需要修改内部逻辑。

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