oracle变异表触发器相关问题-CSDN博客

一个触发器,如下:

CREATE OR REPLACE TRIGGER TRI_KCYXZ1_SFJCHZ
BEFORE update or insert on KCYXZ1
for each row

declare PJLX varchar2(1);
begin
PJLX:=:NEW.KCYXZ1_PJLX;
IF (PJLX='M') THEN
         UPDATE KCYXZ1 SET
         KCYXZ1.KCYXZ1_SFJCHZ='0'
         WHERE KCYXZ1_LSBH=:NEW.KCYXZ1_LSBH;
END IF;

end;

这个触发器,在oracle中compile是没有问题的,但是前台软件使用此表(KCYXZ1)对应的功能的时候,报错(用sql monitor跟踪如下):
----------------------------------
Timestamp: 09:01:38.265
update KCYXZ1 SET KCYXZ1_LSBH =KCYXZ1_LSBH WHERE KCYXZ1_LSBH =:1
:1 = '1053'
Runtime error occurred: 4091 (ORA-04091: 表 LC0039999.KCYXZ1 发生了变化, 触发器/函数不能读它
ORA-06512: 在 "LC0039999.TRI_KCYXZ1_SFJCHZ", line 7
ORA-04088: 触发器 'LC0039999.TRI_KCYXZ1_SFJCHZ' 执行过程中出错)
----------------------------------

从google.com上看了一下这个问题,发现这个是属于变异表（mutating table）的触发器的问题,详细见如下帖子

http://blog.oracle.com.cn/html/58/t-120858.html

此贴子中有如下的描述,我感觉比较有意义,摘录下来:

触发器中的SQL语句不能：

读取或更新触发语句的任何变异表也包括触发表本身，读取或更新该触发表的约束表的主键列、唯一性键列或外键列但是如果需要也可以更新其他列。这些限制约束适用于所有行级触发器。但有个特例就是如果INSERT只影响一行记录那么定义在这行上的行级BEFORE和AFTER触发器就不会将这个触发表当作变异表。

有另外一个帖子说到:

触发器可以包括任何合法的PL／sQL语句，假有以下例外
1.触发器不可以执行COMMIT、ROLLBACK或SAVEPOINT语句，而且不可以调用执行这些语句之一的函数或过程。
2.触发器不可以声明long或LONG RAW变量。
3.触发器不可以在定义它的表上执行DML操作。

我自己写的这个触发器恰恰就违反了"3.触发器不可以在定义它的表上执行DML操作"这个条件.

于是咨询了一下开发工程师,并且在http://www.cnblogs.com/luyongqun/archive/2007/11/19/964470.html帖子的启发下,采用直接赋值的方式,避免了使用update 等dml语句,巧妙的达到了目的.

改后的触发器为(粗体蓝字的为修改了的语句):

CREATE OR REPLACE TRIGGER TRI_KCYXZ1_SFJCHZ
BEFORE update or insert on KCYXZ1
for each row

declare PJLX varchar2(1);
begin
PJLX:=:NEW.KCYXZ1_PJLX;
IF (PJLX='M') THEN
:NEW.KCYXZ1_SFJCHZ:='0';

END IF;

end;

变异表触发器

触发器中SQL语句不能：读取或更新触发语句的任何变异表也包括触发表本身，读取或更新该触发表的约束表的主键列、唯一性键列或外键列
但是如果需要也可以更新其他列。这些限制约束适用于所有行级触发器。(?该触发表的约束表的主键列... ...)
但有个特例就是如果INSERT只影响一行记录那么定义在这行上的行级BEFORE和AFTER触发器就不会将这个触发表当作变异表。
变异表触发器：当向test1表插入数据后，统计test1表中sum(value1)，插入到表test2中。
for example:
create table test1(
  id number(15) not null，
  value1 number(15,2) null
)
create table test2(
  id number(15) not null,
  value2 number(15,2) null
)
--一个简易的TRIGGER先解决你的问题
SQL> create or replace trigger tig_test1
  2 before insert on test1
  3 for each row
  4  declare sumv number(15,2);
  5  begin
  6 select sum(value1) into sumv from test1 where id=:new.id;
  7 sumv := nvl(sumv,0)+:new.value1 ;
  8 insert into test2 (value1) values (sumv);
  9  end tig_test1;
  10  /
Trigger created
SQL> delete from test1
  2  /
1 row deleted
SQL> insert into test1(id,value1) values (1,2)
  2  /
1 row inserted
SQL> select * from test2  2  /
            ID          VALUE1
---------------- -----------------
                           2.00

如何突破变异表？
方法一是用两个触发器，一个行级的，一个语句级的（速度快，推荐！）
方法二是用dbms_job.run包（但运行的时间过长，2秒左右）
方法三是可以使用AutoCommit方法解决（但破坏了事务的一致性，多数情况下是错误的）。
      例如： CREATE OR REPLACE TRIGGER TRIGGER_NAME [BEFORE|AFTER] ACTION ON TABLE_NAME FOR EACH ROW DECLARE PRAGMA AUTONOMOUS_TRANSACTION BEGIN ...... COMMIT; END;
方法一是用两个触发器，一个行级的，一个语句级的:
for example:
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_INTEGER ；
  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;

触发器执行时变异表的问题
有两个表：简单概括起来是这样的：
1、主表pater 。两个字段:
id number; --主键
count number;--计数
2、子表sub。两个字段:
id number; --主键
pid number;--外键。为pater的id字段；删除的时候级连删除
我在sub表上创建了两个触发器；分别在sub表插入和删除记录的
时候更新pater表相应id记录内的count计数；
单独对sub插入/删除一切正常；pater表的计算也正确；
但是;删除pater表内记录时；如果sub表内存在子记录，级连删除时，sub上的触发器试图更新pater的count；就遇到变异表问题；
我对sub表上删除时的触发器使用AUTONOMOUS_TRANSACTION;想这样来避免级连删除时的变异表问题，又出现死锁。
现在我的处理方法是只好在删除pater内记录前先把所有的sub相关子记录删除了；但是感觉太麻烦（估计效率也不好）；
问：
1、能不能在sub表删除的触发器内判断当前是否处于被级连删除的状态，如果能就不更新了

不过估计没有；
2、如果不能；是否有其他的好的方式达到此目的；包括修改表设计；
感觉这个需求很典型的；大家是怎么设计的。能不能告诉小弟或
推荐些资料，真是感激不尽。
3Q!

--==================================================================================================================================
学习变异表
变异表就是当前正被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;
这样就得到了我们想要的结果了！

变异表跟约束表问题
书上有以下一句话:在对其执行DML语句期间,触发体中的SQL语句不能读取或修改触发表的一个约束表的PRIMARY,UNIQUE或FOREIGN关键字的列,然而,如果需求,它们可以修改其他列.
但我试过了,是可以修改PRIMARY列的.为什么?
--------------------------------------------------------------------------------
作者：chenmolin 时间：05-01-27 16:38
真的可以吗？作出来看看。
--------------------------------------------------------------------------------
作者：sbt709394 时间：05-01-27 17:02
SBT表结构如下: SID NUMBER,NAME VARCHAR2(7) PRIMARY KEY,AGE NUMBER
数据如下:
1 AA 1
2 BB 2
3 CC 3
4 D 4
WT表结构如下:NAME VARCHAR2(7) FROEIGN KEY REFERENCES SBT(NAME)
数据如下:
AA
触发器如下:
CREATE OR REPLACE TRIGGER TR BEFORE UPDATE ON WT FOR EACH ROW
BEGIN
UPDATE SBT SET NAME='A' WHERE AGE=1;
END;
/
最后结果:
16:47:10 SQL> UPDATE WT SET NAME='D' WHERE NAME='AA';
已更新 1 行。
16:47:40 SQL> SELECT * FROM SBT;
SID NAME AGE
---------- ------- ----------
1 A 1
2 BB 2
3 CC 3
4 D 4

16:47:46 SQL> SELECT * FROM WT;
NAME
-------
D
--------------------------------------------------------------------------------
作者：sbt709394 时间：05-01-28 17:56
楼上大哥,快来看看................
--------------------------------------------------------------------------------
作者：wnj 时间：05-01-29 08:59
会不会你修改的那个表不满足约束表的条件呢？
--------------------------------------------------------------------------------
作者：wnj 时间：05-01-29 16:54
我找到原因了，也是书上的：
注意，如果只INSERT一行，那么该行的行前和行后触发器就不把触发表看做变异表，这也是行级触发器能过读取或修改触发表的唯一情况。
如INSERT INTO TABLE SELECT ....语句，触发器仍然会把触发表当做变异表看待，即使插入一行也不可以。
不要忘记，UPDATE相当于先删除后插入的操作，而且书上也说了：“这也是行级触发器能过读取或修改触发表的唯一情况”。
那也就是说对触发表一行记录的DML操作在行级触发器是可行的。

--=======================================================================================================
触发器与变异表

触发器与变异表
变异表:当前正在被DML语句更新的表，对于触发器而言，变异表就是在其上定义该触发器的那张表。
触发器中的sql语句不能：（适用于所有行级触发器）
读取或更新触发语句的任何变异表，包括触发表本身。
读取或更新该处发表的约束表的主键列，惟一性健列或外健列，
变异表示例：
将每一门主修课程的学生人数限制在5人，在student表上定义一个before insert or update行级触发器实现
(a)错误示例
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;
按照上面的写法，触发器查询了它自己的触发表，而触发表又是变异表，
修改：
将上面的触发器修改成2个触发器，行级与语句级触发器，在行级触发器中，可以记录 :new.major 的值，但不查询students表
查询在语句级触发器中实现。
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_INTEGER;
V_studentmajors  t_majors;
V_studentids    t_ids;
V_numentries binary_integer:=0;
End studentdata;
/
Create or replace trigger rlimitmajors
Before insert or update of major on students
For each row
Begin
Studentdata.v_numentries:=studentdata.v_numentries+1;
Studentdata.v_studentmajors(studentdata.v_numentries)
:=:new.major;
Studentdata.v_stundentids(studentdata.v_numentries):=
: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 students.id%type;
v_major students.major%type;
begin
for v_loopindex in 1..studentdata.v_numentries 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 in major ’||:new.major);
End if;
End loop;
Studentdata.v_numentries:=0;
End slimitmajors;
说明
1.    确保变量成为全局变量的唯一方法就是将它存放在某个包中。
2.    注意用after语句级触发器中进行处理