如何根据明细表的状态变化，实时修改主表的状态

Question

以采购单为例：两个业务表，一个明细表D，一个主表M
明细表有4个状态：0-取消，1-新单，2-生效，99-结案
当明细表所有项为99-结案时，更新主表的状态为99-结案，表示此采购单已完结。原计划在明细表增加Trigger，,当明细表项的状态转为99时，触发检查整张采购单的项次是否全部为99-结案，如果所有项次全部为99-结案，则更新主表采购单的状态为99-结案。***由于Trigger访问表本身，抛出ORA-04091异常查阅了很多文章，用包变量或用视图触发器，都没能实现上面很简单的业务功能..请问：
1.如果用触发器，要怎么解决：Trigger访问表本身，ORA-04091异常
2.除了触发器，还能用什么方法实现以上业务功能？請幫忙看看咯。。

Answer 1

--应该不难的，给你个例子参考一下或者你把表结构和数据贴出来。
SQL> DESC EMP_TEST;
Name     Type         Nullable Default Comments 
-------- ------------ -------- ------- -------- 
EMPNO    NUMBER(4)    Y                         
ENAME    VARCHAR2(10) Y                         
JOB      VARCHAR2(9)  Y                         
MGR      NUMBER(4)    Y                         
HIREDATE DATE         Y                         
SAL      NUMBER(7,2)  Y                         
COMM     NUMBER(7,2)  Y                         
DEPTNO   NUMBER(2)    Y                         SQL> SELECT * FROM EMP_TEST;EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7369 SMITH      CLERK      7902 1980-12-17     800.00               20Executed in 0.016 seconds-- package 代码:
CREATE OR REPLACE PACKAGE SCOTT.PKG_TRIGGER_TEST  IS
  TYPE T_EMP IS TABLE OF SCOTT.EMP_TEST%ROWTYPE INDEX BY BINARY_INTEGER;
  V_EMPS T_EMP;
  V_NUM BINARY_INTEGER := 0;
END ;-- 行级触发器得发NEW值：
CREATE OR REPLACE TRIGGER SCOTT.GET_EMP_ROW
  AFTER INSERT ON SCOTT.EMP_TEST  
  FOR EACH ROW
BEGIN
  DBMS_OUTPUT.PUT_LINE('-- TRIGGER SCOTT.GET_EMP_ROW START --');
  SCOTT.PKG_TRIGGER_TEST.V_NUM := SCOTT.PKG_TRIGGER_TEST.V_NUM + 1;
  SCOTT.PKG_TRIGGER_TEST.V_EMPS(SCOTT.PKG_TRIGGER_TEST.V_NUM).EMPNO := :NEW.EMPNO;
  SCOTT.PKG_TRIGGER_TEST.V_EMPS(SCOTT.PKG_TRIGGER_TEST.V_NUM).ENAME := :NEW.ENAME;
  SCOTT.PKG_TRIGGER_TEST.V_EMPS(SCOTT.PKG_TRIGGER_TEST.V_NUM).JOB := :NEW.JOB;
  SCOTT.PKG_TRIGGER_TEST.V_EMPS(SCOTT.PKG_TRIGGER_TEST.V_NUM).MGR := :NEW.MGR;
  SCOTT.PKG_TRIGGER_TEST.V_EMPS(SCOTT.PKG_TRIGGER_TEST.V_NUM).HIREDATE := :NEW.HIREDATE;
  SCOTT.PKG_TRIGGER_TEST.V_EMPS(SCOTT.PKG_TRIGGER_TEST.V_NUM).SAL := :NEW.SAL;
  SCOTT.PKG_TRIGGER_TEST.V_EMPS(SCOTT.PKG_TRIGGER_TEST.V_NUM).COMM := :NEW.COMM;
  SCOTT.PKG_TRIGGER_TEST.V_EMPS(SCOTT.PKG_TRIGGER_TEST.V_NUM).DEPTNO := :NEW.DEPTNO;
  
EXCEPTION
  WHEN OTHERS THEN
       DBMS_OUTPUT.PUT_LINE(SQLCODE||' : '||SQLERRM);
  
END GET_EMP_ROW;-- 表级触发器得到最大工资等Operation:
CREATE OR REPLACE TRIGGER SCOTT.TIA_T_RESULT_HEAD
  AFTER INSERT ON SCOTT.EMP_TEST
DECLARE
  -- local variables here
  V_EMP_NO NUMBER;
  V_MANAGER_NO NUMBER;
  V_EMP_DATE DATE;
  V_DEPT_NO NUMBER;
  
  V_MAX_SALARY SCOTT.EMP_TEST.SAL%TYPE;
  
  V_STEP VARCHAR2(100) := '';
BEGIN
  DBMS_OUTPUT.PUT_LINE('-- TRIGGER SCOTT.TIA_T_RESULT_HEAD START --');
  V_STEP := 'GET NEW VALUES';
  FOR I IN 1..SCOTT.PKG_TRIGGER_TEST.V_EMPS.COUNT LOOP
      V_EMP_NO     := SCOTT.PKG_TRIGGER_TEST.V_EMPS(I).EMPNO;
      V_MANAGER_NO := SCOTT.PKG_TRIGGER_TEST.V_EMPS(I).MGR;
      V_EMP_DATE   := SCOTT.PKG_TRIGGER_TEST.V_EMPS(I).HIREDATE;
      V_DEPT_NO    := SCOTT.PKG_TRIGGER_TEST.V_EMPS(I).DEPTNO;
      
      DBMS_OUTPUT.PUT_LINE('EMP_NO: '||V_EMP_NO||' MANAGER_NO: '||V_MANAGER_NO||' EMP_DATE: '||V_EMP_DATE||' DEPT_NO: '||V_DEPT_NO);
      
      V_STEP := 'GET MAX SALARY';
      SELECT NVL(MAX(SAL),0) 
        INTO V_MAX_SALARY 
        FROM SCOTT.EMP_TEST
       WHERE DEPTNO = V_DEPT_NO;
      
      V_STEP := 'PRINT MAX SALARY AFTER INSERTED RECORDS';
      DBMS_OUTPUT.PUT_LINE('MAX SALARY: '||V_MAX_SALARY);
      DBMS_OUTPUT.PUT_LINE('');
      
  END LOOP;
  
EXCEPTION
  WHEN OTHERS THEN
       DBMS_OUTPUT.PUT_LINE('Error at : '||V_STEP);
       DBMS_OUTPUT.PUT_LINE(SQLCODE||' : '||SQLERRM);
END TIA_T_RESULT_HEAD;-- 测试结果:
SQL> INSERT INTO SCOTT.EMP_TEST
  2  SELECT 7768,'MANTISXF','SALESER',7369,SYSDATE,3000,NULL,30 FROM DUAL
  3  UNION ALL
  4  SELECT 7780,'FENG','MONTIORER',7368,SYSDATE,5000,NULL,20 FROM DUAL
  5  ;-- TRIGGER SCOTT.GET_EMP_ROW START --
-- TRIGGER SCOTT.GET_EMP_ROW START --
-- TRIGGER SCOTT.TIA_T_RESULT_HEAD START --
EMP_NO: 7768 MANAGER_NO: 7369 EMP_DATE: 28-9月 -08 DEPT_NO: 30
MAX SALARY: 3000EMP_NO: 7780 MANAGER_NO: 7368 EMP_DATE: 28-9月 -08 DEPT_NO: 20
MAX SALARY: 5000
2 rows insertedExecuted in 0.25 secondsSQL> COMMIT;Commit completeExecuted in 0 secondsSQL> SELECT * FROM EMP_TEST;EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7369 SMITH      CLERK      7902 1980-12-17     800.00               20
 7768 MANTISXF   SALESER    7369 2008-9-28 1   3000.00               30
 7780 FENG       MONTIORER  7368 2008-9-28 1   5000.00               20Executed in 0.031 secondsSQL> INSERT INTO SCOTT.EMP_TEST VALUES(8866,'CSDN','MANAGER',7369,SYSDATE,8000,NULL,10);-- TRIGGER SCOTT.GET_EMP_ROW START --
-- TRIGGER SCOTT.TIA_T_RESULT_HEAD START --
EMP_NO: 7768 MANAGER_NO: 7369 EMP_DATE: 28-9月 -08 DEPT_NO: 30
MAX SALARY: 3000EMP_NO: 7780 MANAGER_NO: 7368 EMP_DATE: 28-9月 -08 DEPT_NO: 20
MAX SALARY: 5000EMP_NO: 8866 MANAGER_NO: 7369 EMP_DATE: 28-9月 -08 DEPT_NO: 10
MAX SALARY: 8000
1 row insertedExecuted in 0.015 secondsSQL> COMMIT;Commit completeExecuted in 0.015 secondsSQL> SELECT * FROM EMP_TEST;EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7369 SMITH      CLERK      7902 1980-12-17     800.00               20
 7768 MANTISXF   SALESER    7369 2008-9-28 1   3000.00               30
 7780 FENG       MONTIORER  7368 2008-9-28 1   5000.00               20
 8866 CSDN       MANAGER    7369 2008-9-28 1   8000.00               10Executed in 0.015 seconds

Answer 2

mantisXF老大给的方案好复杂哦，有没有简单点的。。@_@#

Answer 3

目前为止，想实现必须用PACKAGE可变数组+行级触发器＋表级触发器。

Answer 4

--1.--
--建包變量
-- package 代码:
CREATE OR REPLACE PACKAGE SC00.GG_PO IS
  TYPE T_POITEM IS TABLE OF SC00.PO_ORD_ITEM%ROWTYPE INDEX BY BINARY_INTEGER;
  V_POITEM T_POITEM;
  V_NUM BINARY_INTEGER := 0;
END;
-----------------------------------------   --2.--建行級觸發器保存值
CREATE OR REPLACE TRIGGER GET_POITEM_ROW
  AFTER UPDATE OR DELETE ON PO_ORD_ITEM  
  FOR EACH ROW
BEGIN
  DBMS_OUTPUT.PUT_LINE('-- TRIGGER SC00.GET_POITEM_ROW START --');
  SC00.GG_PO.V_NUM := SC00.GG_PO.V_NUM + 1;
  SC00.GG_PO.V_POITEM(SC00.GG_PO.V_NUM).ORG_ID := :NEW.ORG_ID;
  SC00.GG_PO.V_POITEM(SC00.GG_PO.V_NUM).PO_ID := :NEW.PO_ID;
  SC00.GG_PO.V_POITEM(SC00.GG_PO.V_NUM).PO_SEQ := :NEW.PO_SEQ;
  SC00.GG_PO.V_POITEM(SC00.GG_PO.V_NUM).STATUS := :NEW.STATUS;
   
EXCEPTION
  WHEN OTHERS THEN
       DBMS_OUTPUT.PUT_LINE('Error at : GET_POITEM_ROW');
       DBMS_OUTPUT.PUT_LINE(SQLCODE||' : '||SQLERRM);
  
END;
--3.--建表級觸發器更新主表PO_ORD_M
CREATE OR REPLACE TRIGGER UPDATE_PO_ORD_M_STATUS_29
  AFTER UPDATE OR DELETE ON PO_ORD_ITEM  
DECLARE
  -- local variables here
  V_ORGID NUMBER(6);
  V_POID VARCHAR2(30);
  V_POSEQ NUMBER(2);
  V_STATUS NUMBER(2);
  
  V_PO_COUNT NUMBER;
  V_COUNT NUMBER;
  
  V_STEP VARCHAR2(100) := '';BEGIN
  --DBMS_OUTPUT.PUT_LINE('-- TRIGGER SC00.PO_ITEM_STATUS_COUNT START --');
  V_STEP := 'GET NEW VALUES';
  FOR I IN 1..GG_PO.V_POITEM.COUNT LOOP
      V_ORGID     := GG_PO.V_POITEM(I).ORG_ID;
      V_POID := GG_PO.V_POITEM(I).PO_ID;
      V_POSEQ   := GG_PO.V_POITEM(I).PO_SEQ;
      V_STATUS    := GG_PO.V_POITEM(I).STATUS;
      
      IF (V_STATUS=0 OR V_STATUS=29) THEN 
      --DBMS_OUTPUT.PUT_LINE('PO_ID: '||V_POID||' PO_SEQ: '||V_POSEQ||' STATUS: '||V_STATUS);
      
      V_STEP := 'GET ORDER ITEM COUNT';
      SELECT COUNT(1)
        INTO V_PO_COUNT
        FROM PO_ORD_ITEM
       WHERE ORG_ID = V_ORGID
       AND PO_ID = V_POID;
      
      V_STEP := 'PRINT ORDER ITEM COUNT';
      --DBMS_OUTPUT.PUT_LINE('ORDER ITEM COUNT: '||V_PO_COUNT);
      --DBMS_OUTPUT.PUT_LINE('');
      
      V_STEP := 'GET ORDER ITEM STATUS NOT 0 OR 29 COUNT';
      SELECT COUNT(1)
        INTO V_COUNT
        FROM PO_ORD_ITEM
       WHERE ORG_ID = V_ORGID
       AND PO_ID = V_POID
       AND STATUS NOT IN (0,29);
      
      V_STEP := 'PRINT ORDER ITEM STATUS NOT in 0 OR 29 COUNT';
      --DBMS_OUTPUT.PUT_LINE('ORDER ITEM STATUS NOT in 0 OR 29 COUNT: '||V_COUNT);
      --DBMS_OUTPUT.PUT_LINE('');
      
       --如果沒有其他狀態,則更新采購單主檔狀態
     IF (NVL(V_PO_COUNT,0)>0 AND NVL(V_COUNT,0)=0) THEN
                          
               SELECT COUNT(1) INTO V_COUNT
               FROM   PO_ORD_ITEM
               WHERE ORG_ID= V_ORGID
              AND PO_ID= V_POID
              AND STATUS=0;
             
        --訂單項全部為取消狀態
        IF NVL(V_COUNT,0) = NVL(V_PO_COUNT,0) THEN
            UPDATE  PO_ORD_M
            SET STATUS=0
            WHERE ORG_ID= V_ORGID
            AND PO_ID= V_POID
            AND STATUS<>0;
            
           V_STEP := 'UPDATE PO BE CANCEL';
           --DBMS_OUTPUT.PUT_LINE(V_STEP);
           --DBMS_OUTPUT.PUT_LINE('');
      
       ELSE
            --更新采購單為完結
            UPDATE  PO_ORD_M
            SET STATUS=29
            WHERE ORG_ID= V_ORGID
            AND PO_ID= V_POID
            AND STATUS<>29;
           
            V_STEP := 'UPDATE PO BE CLOSED 29';
           --DBMS_OUTPUT.PUT_LINE(V_STEP);
           --DBMS_OUTPUT.PUT_LINE(''); 
       END IF;
     END IF; 
   END IF;
  END LOOP;
  
EXCEPTION
  WHEN OTHERS THEN
       DBMS_OUTPUT.PUT_LINE('Error at : '||V_STEP);
       DBMS_OUTPUT.PUT_LINE(SQLCODE||' : '||SQLERRM);
END;