学习变异表
变异表就是当前正被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;
这样就得到了我们想要的结果了！
oracle中变异表触发器的处理 作者： 来源：
阅读 1265 人次 , 2006-4-13 16:27:00
可以使用AutoCommit方法解决。例如： CREATE OR REPLACE TRIGGER TRIGGER_NAME [BEFORE|AFTER] ACTION ON TABLE_NAME FOR EACH ROW DECLARE PRAGMA AUTONOMOUS_TRANSACTION BEGIN ...... COMMIT; END;
关于在触发器中操作便异表[最近咨询变异表的人很多]
变异表就是当前被DML语句修改的表，对trigger来说，变异表就是trigger在其上
定义的表。需要明确的是trigger中SQL语句不能进行如下操作：
1）读或修改触发语句的任何变异表,其中包括触发表本身
2）读或修改触发表的约束表中的主关键字，唯一关键字和外部关键字列。除此之外的其他列都可以修改。
以上2条限制适合所有的行级trigger.
注意：如果insert 语句只影响一句的话，则在改行的before和after 触发器将不把触发表当作变异表对待。
这是在行级trigger可能载入或修改触发表时的唯一案例。而对于象insert into table_name select * from ...
总是把触发表当作变异表，即使子查询返回一行也是一样的。
下面以一个具体的实例来说明这一点
PHP code:--------------------------------------------------------------------------------
SQL> create table toms(area number(3),
2 name varchar2(32));
表已创建。
SQL> insert into toms values(10,'BJ');
已创建 1 行。
SQL> commit;
提交完成。
SQL> create or replace trigger trig_bi_toms
2 before insert on toms
3 for each row
4 begin
5 if :new.area=531 then
6 update toms set name='调整' where area=531;
7 end if;
8 end;
9 /
触发器已创建
SQL> insert into toms values(20,'TJ');
已创建 1 行。
SQL> commit;
提交完成。
SQL> insert into toms values(531,'JN');
已创建 1 行。
SQL> commit;
提交完成。
SQL> select * from toms;
10 BJ
20 TJ
531 JN
SQL> insert into toms values(531,'JN');
已创建 1 行。
SQL> commit;
提交完成。
SQL> select * from toms;
10 BJ
20 TJ
531 调整
531 JN
SQL>
SQL> insert into toms select 531,'DH' from tabs;
insert into toms select 531,'DH' from tabs
*
第 1 行出现错误:
ORA-04091: 表 STUDY.TOMS 发生了变化, 触发器/函数不能读它
ORA-06512: 在 "STUDY.TRIG_BI_TOMS", line 3
ORA-04088: 触发器 'STUDY.TRIG_BI_TOMS' 执行过程中出错
SQL> insert into toms select 531,'DH' from tabs where rownum=1;
insert into toms select 531,'DH' from tabs where rownum=1
*
第 1 行出现错误:
ORA-04091: 表 STUDY.TOMS 发生了变化, 触发器/函数不能读它
ORA-06512: 在 "STUDY.TRIG_BI_TOMS", line 3
ORA-04088: 触发器 'STUDY.TRIG_BI_TOMS' 执行过程中出错
SQL>
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7600171/viewspace-545605/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/7600171/viewspace-545605/
扫一扫
474
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
