遇到这么一个需求，同一张表里，相同的内容，分别存在两个类别（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，我想也是得根据你的业务逻辑，来自行定的吧，不知有没有放之四海而皆准的写法？