ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 解决ORA-04091: mutating table issue

解决ORA-04091: mutating table issue

原创 Linux操作系统 作者:phoenixbai 时间:2011-08-18 14:37:00 0 删除 编辑

遇到这么一个需求,同一张表里,相同的内容,分别存在两个类别(ZJMI和 NAV)下面。界面上,只允许客户通过UI只可修改ZJMI下面的数据,且ZJMI下面的任数变动,要同时同步到NAV下面去。

这若用after trigger at row level的话,注定要遇到ORA-04091:MUTATING TABLE 的问题。我的解决方法如下,为方便,就拿EMP来做个例子吧。

1)在包内定义好将在TRIGGER中要用到的变量。

2)在BEFORE TRIGGER中,将变量的值设为空,以免上次失败的操作,影响这次的操作的结果。

3)在AFTER TRIGGER AT ROW LEVEL中,存下来DML操作中相关数据。具体,我这边是:

INSERT: 只存ROWID.因为是新数据。

UPDATE:存所有的新的值。(假设任何一条记录,都有一个或几个字段可用来标识其唯一性,即,有PRIMARY KEY,且它是不变的。)

DELETE:存所有的老的值。

4)在AFTER TRIGGER AT STATEMENT LEVEL中,进行对应的DML操作。

思路讲清了,现在贴代码了:

1)

create or replace
package state_pkg_for_trigger
     as
             type RIDARRAY is table of rowid index by BINARY_INTEGER;
             type emparray is table of emp%rowtype index by binary_integer;

             NEWROWS RIDARRAY;
             upDEMP EMPARRAY;
            OLDEMP EMPARRAY;
             EMPTYROWS   RIDARRAY;
             emptyemp emparray;
            
     end state_pkg_for_trigger;

2)

create or replace
TRIGGER TRG_RESET_STATE
BEFORE INSERT OR DELETE OR UPDATE ON EMP
begin
    STATE_PKG_FOR_TRIGGER.NEWROWS := STATE_PKG_FOR_TRIGGER.EMPTYROWS;
    STATE_PKG_FOR_TRIGGER.OLDEMP := STATE_PKG_FOR_TRIGGER.EMPTYEMP;
    STATE_PKG_FOR_TRIGGER.UPDEMP := STATE_PKG_FOR_TRIGGER.emptyEMP;
END;

 

3)

create or replace
TRIGGER TRG_STORE_ROWIDS
AFTER INSERT OR DELETE OR UPDATE ON EMP
for each row
when (old.CATEGRY='ZJMI' or new.CATEGRY='ZJMI')

declare
  I number default STATE_PKG_FOR_TRIGGER.UPDEMP.COUNT+1;
  j number default STATE_PKG_FOR_TRIGGER.OLDEMP.COUNT+1;
BEGIN
      if inserting
  then
   state_pkg_for_trigger.newRows( state_pkg_for_trigger.newRows.count+1 ) := :new.rowid;
  end if;

  if UPDATING
  then
   STATE_PKG_FOR_TRIGGER.UPDEMP(I).EMPNO := :new.EMPNO;
      STATE_PKG_FOR_TRIGGER.UPDEMP(I).ENAME := :new.ENAME;
      STATE_PKG_FOR_TRIGGER.UPDEMP(I).JOB := :new.JOB;
      STATE_PKG_FOR_TRIGGER.UPDEMP(I).MGR := :new.MGR;
      STATE_PKG_FOR_TRIGGER.UPDEMP(I).HIREDATE := :new.HIREDATE;
      STATE_PKG_FOR_TRIGGER.UPDEMP(I).SAL := :new.SAL;
      STATE_PKG_FOR_TRIGGER.UPDEMP(I).COMM := :new.COMM;
      STATE_PKG_FOR_TRIGGER.UPDEMP(I).DEPTNO := :new.DEPTNO;
      STATE_PKG_FOR_TRIGGER.UPDEMP(I).CATEGRY := :new.CATEGRY;
     
  end if;

  if deleting
  then
      STATE_PKG_FOR_TRIGGER.OLDEMP(J).EMPNO := :OLD.EMPNO;
            STATE_PKG_FOR_TRIGGER.OLDEMP(J).ENAME := :OLD.ENAME;
            STATE_PKG_FOR_TRIGGER.OLDEMP(J).JOB := :OLD.JOB;
            STATE_PKG_FOR_TRIGGER.OLDEMP(J).MGR := :OLD.MGR;
            STATE_PKG_FOR_TRIGGER.OLDEMP(J).HIREDATE := :OLD.HIREDATE;
            STATE_PKG_FOR_TRIGGER.OLDEMP(J).SAL := :OLD.SAL;
            STATE_PKG_FOR_TRIGGER.OLDEMP(J).COMM := :OLD.COMM;
            STATE_PKG_FOR_TRIGGER.OLDEMP(J).DEPTNO := :OLD.DEPTNO;
            STATE_PKG_FOR_TRIGGER.OLDEMP(J).CATEGRY := :OLD.CATEGRY;
  end if;
END;

 

4)

create or replace
TRIGGER TRG_CAPTURE_EMP_CHANGE
after insert or delete or update on EMP
declare
   V_CATEGRY VARCHAR2(5) := 'NAV';
   V_COUNT number :=0;
   V_ERR varchar2(400);
   V_EMP EMP%ROWTYPE := NULL;
  
begin
       --FOR INSERT
      -- V_COUNT := state_pkg_for_trigger.newRows.count;
   
    for i in 1 .. state_pkg_for_trigger.newRows.count
    loop
           insert into EMP (
        EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, CATEGRY  )
            select  EMPNO, ENAME, job, MGR, HIREDATE, SAL, COMM, DEPTNO, V_CATEGRY
           from EMP where rowid = state_pkg_for_trigger.newRows(i);           
          
       end loop;
            
     
      --FOR UPDATE
      for I in 1 .. STATE_PKG_FOR_TRIGGER.UPDEMP.COUNT
      LOOP
        V_EMP := STATE_PKG_FOR_TRIGGER.UPDEMP(I);
     
        update EMP set ENAME=V_EMP.ENAME,
                      job=V_EMP.job,
                      MGR=V_EMP.MGR,
                      HIREDATE=V_EMP.HIREDATE,
                      DEPTNO=V_EMP.DEPTNO
        where EMPNO=V_EMP.EMPNO
              AND CATEGRY=V_CATEGRY;
     
     
      END LOOP;
     
      V_EMP := NULL;
     
      --FOR DELETE   
     --  V_COUNT := STATE_PKG_FOR_TRIGGER.oldEMP.COUNT;
      
    for i in 1 .. state_pkg_for_trigger.oldEMP.count
    loop
       
        V_EMP := STATE_PKG_FOR_TRIGGER.OLDEMP(I);
       
    delete from EMP
    where EMPNO = V_EMP.EMPNO
      and ENAME= V_EMP.ENAME
      and job = V_EMP.job
      and MGR = V_EMP.MGR
      and HIREDATE = V_EMP.HIREDATE
      and DEPTNO = V_EMP.DEPTNO
           AND CATEGRY=V_CATEGRY;
   
    end LOOP;
 
 /* 
     EXCEPTION
     when OTHERS
     then
        DBMS_OUTPUT.PUT_LINE(SQLERRM);
       
        */
END;

不同的问题有不同的解决方案。我这同时要捕捉INSERT, UPDATE, DELETE,所以写得复杂了点。针对于UPDATE要说明的是,我知道VALID_FLAG=1的记录才能更新,所以才敢写死的。你们的UPDATE,我想也是得根据你的业务逻辑,来自行定的吧,不知有没有放之四海而皆准的写法?

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10223243/viewspace-705258/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2011-01-02

  • 博文量
    2
  • 访问量
    14942