变异表解决方法

对于已发出DML语句的表上定义一个触发器，触发器试图读取或修改该表，会导致变异表出错。在触发器体中发出的SQL语句可能无法读取或修改一个变异表，此限制适用于行级触发器。
引起变异表错误的触发器实例：

CREATE OR REPLACE TRIGGER section_biu
BEFORE INSERT OR UPDATE ON section
FOR EACH ROW
DECLARE
 v_total NUMBER;
 v_name VARCHAR2(30);
BEGIN
  SELECT COUNT(*)
  INTO v_total
  FROM section --变异表
  WHERE instructor_id=:NEW.instructor_id;
	--检查当前老师是否被超量预订
	IF v_total>10
	THEN
	  SELECT first_name||' '||last_name INTO v_name
	  FROM instructor
	  WHERE instructor_id=:NEW.instructor_id;
	  RAISE_APPLICATION_ERROR(-20000,v_name ||' is overbooked');
	END IF;
EXCEPTION
  WHEN NO_DATA_FOUND 
  THEN
   RAISE_APPLICATION_ERROR(-20001,'this is an invalid instructor');
END;

对现在已有该触发器的表进行更新：
UPDATE section SET instructor_id=101 WHERE section_id=10;
出现ORA-04091报错,主要由以下语句造成：

 SELECT COUNT(*)
  INTO v_total
  FROM section --变异表
  WHERE instructor_id=:NEW.instructor_id;

解决方法：
1、在11g之前oracle版本，使用全局变量方法：

• 创建了一个声明俩个全局变量的包

CREATE OR REPLACE PACKAGE instructor_pack
AS g_instructor_id instructor.instructor_id%TYPE;
   g_instructor_name varchar2(128);
END;

• 修改现有触发器来记录教师ID和姓名

CREATE OR REPLACE TRIGGER section_biu
BEFORE UPDATE OR INSERT ON section
FOR EACH ROW
IF :NEW.instructor_id IS NOT NULL
 THEN
  BEGIN
   instructor_pack.g_instructor_id := :NEW.instructor_id;
   SELECT first_name||' '||last_name INTO instructor_pack.g_instructor_name 
   FROM instructor
   WHERE instructor_id=instructor_pack.g_instructor_id;
  EXCEPTION
   WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR(-20001,'this is an invalid instructor');
  END;
END IF;

• 创建一个语句触发器

CREATE OR REPLACE TRIGGER section_aiu
AFTER INSERT OR UPDATE ON section
DECLARE
 v_total integer;
BEGIN
 SELECT COUNT(*) INTO v_total
 FROM section
 WHERE instructor_id=instructor_pack.g_instructor_id;
 --检查当前教师是否被超量预订
 IF v_total > 10 THEN
  RAISE_APPLICATION_ERROR(-20000,instructor_pack.g_instructor_name||' is overbooked');
 END IF;
END;

现在执行更新section操作，出现预期的结果：
ORA-20000:(表中姓名) is overbooked;

2、使用复合触发器

CREATE OR REPLACE TRIGGER section_compound
FOR INSERT OR UPDATE ON section
COMPOUND TRIGGER
  --声明部分
  v_instructor_id INSTRUCTOR.INSTRUCTOR_ID%TYPE;
  v_name VARCHAR2(128);
  v_total INTEGER;
 --在触发语句影响每一行之前触发的行触发器
 BEFORE EACH ROW IS
  BEGIN
   IF :NEW.instructor_id IS NOT NULL
   THEN
    BEGIN
     v_instructor_id := :NEW.instructor_id;
     SELECT first_name||' '||last_name INTO v_name
     FROM instructor
     WHERE instructor_id = v_instructor_id;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        RAISE_APPLICATION_ERROR(-20001,'this is an invalid instructor');
    END;
   END IF;
  END BEFORE EACH ROW;
 --在触发语句之后触发的语句触发器
 AFTER STATEMENT IS 
 BEGIN
  SELECT COUNT(*) INTO v_total
  FROM section
  WHERE instructor_id = v_instructor_id;
 IF v_total > 10 THEN
  RAISE_APPLICATION_ERROR(-20000,v_name||' is overbooked');
 END IF;
END AFTER STATEMENT;

END;