Oracle变异表触发器中ORA-04091错误原因及解决方案

变异表是指激发触发器的DML语句所操作的表

当对一个表创建行级触发器时，有下列两条：

1.不能读取或修改任何触发语句的变异表；
2.不能读取或修改触发表的一个约束表的PRIMARY KEY，UNIQUE 或FOREIGN KEY关键字的列, 但可以修改其他列

例如：有这样一个需求：在更新员工所在部门或向部门插入新员工时，部门中员工人数不超过7人

如果按照下面的触发器写就会使UPDATE操作时报错

CREATE OR REPLACE TRIGGER updatetrigger
BEFORE UPDATE ON EMP
FOR EACH ROW
DECLARE
v_num NUMBER;
BEGIN
SELECT count(*) INTO v_num FROM emp
WHERE deptno = :new.deptno;
IF (v_num > 7) THEN
RAISE_APPLICATION_ERROR(-20001,
'员工数多于'||v_num);
END IF;
END updatetrigger;

ORA-04091: 表 SCOTT.EMP 发生了变化, 触发器/函数不能读它
ORA-06512: 在 "SCOTT.UPDATETRIGGER", line 4
ORA-04088: 触发器 'SCOTT.UPDATETRIGGER' 执行过程中出错

如果既想更新变异表，同时又需要查询变异表，那么如何处理呢?

将行级触发器与语句级触发器结合起来，，在行级触发器中获取要修改的记录的信息，存放到一个软件包的全局变量中，然后在语句级后触发器中利用软件包中全局变量信息对变异表的查询，并根据查询的结果进行业务处理

例如：

为了实现在更新员工所在部门或向部门插入新员工时，部门中员工人数不超过7人，可以在emp表上创建两个触发器，同时创建一个共享信息的包

CREATE OR REPLACE PACKAGE mutate_pkg
AS
v_deptno NUMBER(2);
END;

CREATE OR REPLACE TRIGGER rmutate_trigger
BEFORE INSERT OR UPDATE OF deptno ON EMP
FOR EACH ROW
BEGIN
mutate_pkg.v_deptno:=:new.deptno;
END;

CREATE OR REPLACE TRIGGER smutate_trigger
AFTER INSERT OR UPDATE OF deptno ON EMP
DECLARE
v_num number(3);
BEGIN
SELECT count(*) INTO v_num FROM emp
WHERE deptno = mutate_pkg.v_deptno;
IF v_num>7 THEN
RAISE_APPLICATION_ERROR(-20003,'这部门的员工太多了 '||
mutate_pkg.v_deptno);
END IF;
END;

这样操作，就不会报ORA-04091: 表SCOTT.EMP 发生了变化,触发器/函数不能读它错误了。

相关阅读：

Oracle触发器的使用

Oracle触发器给表自身的字段重新赋值出现ORA-04091异常

Oracle创建触发器调用含参数存储过程

Oracle触发器查询统计本表