行级触发器相关的变异表

1、变异表定义：

              变异表就是当前正被DML语句更新的表；（一些行修改一些行未修改时读取表而导致的变异表问题）
2、环境准备：

     为了更好的阐述变异表考虑下面3个表：students,classes,registered_students 表students和classes都没有依赖关系但是表registered_students有两个外键一个依赖于students表的主键一个依赖于classes的主键 表如下：
create table students (
  id   number(5) not null,
  current_credits   number(3),
  major             varchar2(20),
  last_name         varchar2(20) not null,
  first_name        varchar2(20) not null,
  middle_initial    varchar2(20) not null,
  constraint students_pk primary key (id));
create table classes (
  department        char(3) not null,
  course            number(3) not null,
  current_students  number(3) not null,
  num_credits       number(1) not null,
  name              varchar2(30) not null,
  constraint classes_pk primary key (department,course));
create table registered_students (
  student_id  number(5) not null,
  department  char(3)   not null,
  course      number(3) not null,
  grade       char(1),
  constraint rs_grade check (grade in ('a,','b','c','d','f')),
  constraint re_student_id foreign key  (student_id) references students (id),
  constraint re_department_course foregn key (department,course) references classes (department,course));
表registered_students 上声明了2个引用完整性约束，因此表students和classes都是registered_students 的约束表，由于存在这种约束所以表students和classes可能会需要使用DML语句进行更新和查询，另外在registered_students表上执行DML语句的时候它就变成

 

3、变异表引起问题的2个场景：

1）行级触发器中读取或更新了变异表本身（变异表是由本表上的触发语句dml产生）；

2）行级触发器中读取或更新了有外键依赖关系的主表上的主键列

特例：如果INSERT只影响一行记录那么定义在这行上的行级BEFORE和AFTER触发器就不会将这个触发表当作变异表。

eg1、作为实例可以考虑下面这个触发器即使他更新了students和classes这两个表但是依然是合法的因为更新的列都不是主键列
create or replace trigger cascadersinserts
  before insert on registered_students
  for each row
declare
  v_credits  classes.num_credits%type;
begin
  select num_credits into v_credits from classes where department=:new.department and course=:new.course;
  update students set current_credits=current_credits+v_credits where id=:new.student_id;
  update classes set current_students=current_students+1 where department=:new.department and course=:new.course;
end cascadersinserts;
eg2、变异表示例
假定希望将每一门主修课程的学生人数限制在5人，可以在students表上定义一个行级触发器来实现这个任务
create or replace trigger limitmajors
  before insert or update of major on students
  for each row
declare
  v_maxstudents constant number:=5;
  v_currentstudents number;
begin
  select count(*) into v_currentstudents from students where major=:new.major;
  if v_currentstudents+1>v_maxstudents then
     raise_application_error(-20000,'too many students in major'||:new.major);
  end if;
end limitmajors;

4、解决办法：

1）分割为一个before行级记录信息触发器+after语句级触发器

初看之下似乎实现了这个目标但是会产生错误原因是触发器查询了它自己的触发表而该触发表又是一个变异表
那么要想实现就意味这不能在行级触发器里查询该表，但是可以在语句级触发器里查询它。但是不能简单的将它设计成一个语句级触发器因为我们需要在触发器主体中使用:new.major的值。这个问题的解决办法就是创建2个触发器一个行级的一个语句的，在行级里记录:new.major的值但是不查询表，查询在语句里实现。那么怎么记录值呢，就要通过一个包了。
create or replace package studentdata as
  type t_majors is table of students.major%type index by binary_integer;
  type t_ids is table of students.id%type index by binary
  v_studentmajors t_majors;
  v_studentids    t_ids;
  v_numbertries   binary_integer:=0;
end studentsdata;
create or replace trigger rlimitmajors
  before insert or update of major on students
  for each row
begin
  studentdata.v_numbertries:=studentdata.v_numbertries+1;
  studentdata.v_studentmajors(studentdata.v_numberies):=:new.major;
  studentdata.v_studentids(studentdata.v_numberies):=:new.id;
end rlimitmajors;
create or replace trigger slimitmajors
  after insert or update of major on students
declare
  v_maxstudents constant number:=2;
  v_currentstudents number;
  v_studentid student.id%type;
  v_major students.major%type;
begin
  for v_loopindex in 1..studentdata.v_numberies loop
      v_studentid:=studentdata.v_studentids(v_loopindex);
      v_major:=studentdata.v_studentmajors(v_loopindex);
  select count(*) into v_currentstudents from students where major=v_major;
  if v_currentstudents>v_maxstudents then
     raise_application_error(-20000,'too many students for major '||v_major||'because of student'||v_studentid);
  end if;
  studentdata.v_numberies:=0;
end slimitmajors;
2）使用自制自动提交事务