变异表就是当前正被DML语句更新的表
为了更好的阐述变异表考虑下面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语句的时候它就变成一个变异表
触发器中SQL语句不能：读取或更新触发语句的任何变异表也包括触发表本身，读取或更新该触发表的约束表的主键列、唯一性键列或外键列但是如果需要也可以更新其他列。这些限制约束适用于所有行级触发器。但有个特例就是如果INSERT只影响一行记录那么定义在这行上的行级BEFORE和AFTER触发器就不会将这个触发表当作变异表。
作为实例可以考虑下面这个触发器即使他更新了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;
变异表示例
假定希望将每一门主修课程的学生人数限制在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;
初看之下似乎实现了这个目标但是会产生错误原因是触发器查询了它自己的触发表而该触发表又是一个变异表
那么要想实现就意味这不能在行级触发器里查询该表，但是可以在语句级触发器里查询它。但是不能简单的将它设计成一个语句级触发器因为我们需要在触发器主体中使用: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;
这样就得到了我们想要的结果了