ORA-04091 觸發器/函式不能讀

 

報錯如下：

SQL> update GPPAYMENTFUND set attribute5='1' where fundapplyno ='20120314500102010001';
 
update GPPAYMENTFUND set attribute5='1' where fundapplyno ='20120314500102010001'
 
ORA-04091: 表 ACDEP.GPPAYMENTFUND 發生了變化，觸發器/函式不能讀
ORA-06512: 在"ACDEP.CLM_WEB_MAIN", line 4
ORA-04088: 觸發器 'ACDEP.CLM_WEB_MAIN' 執行過程中出錯

 

觸發器如下：

CREATE OR REPLACE TRIGGER CLM_WEB_MAIN
before UPDATE
OF ATTRIBUTE5
ON ACDEP.GPPAYMENTFUND 
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE v_id NUMBER;v_row_id VARCHAR2(30);v_change_type CHAR(1);
BEGIN
  IF UPDATING  THEN
        SELECT B.C_EDR_NO INTO v_row_id FROM acdep.GPPAYMENTFUND  A,acdep.T_FIN_CAVDOC B
            WHERE A.FUNDAPPLYNO=B.C_CAV_NO AND A.FUNDAPPLYNO=:NEW.FUNDAPPLYNO AND SUBSTR(B.C_EDR_NO,0,2)='03';
     IF(SUBSTR(v_row_id,0,4)='0330') THEN
        UPDATE SET T_JQ_PAY_TM=TO_DATE (:NEW.ATTRIBUTE5,'yyyy-mm-dd hh24:mi:ss')
            WHERE T_CLAIM_NO=v_row_id and T_JQ_PAY_TM is null;
     ELSIF(SUBSTR(v_row_id,0,4)='0332') THEN
        UPDATE SET T_SY_PAY_TM=TO_DATE (:NEW.ATTRIBUTE5,'yyyy-mm-dd hh24:mi:ss')
            WHERE B_CLAIM_NO=v_row_id and T_SY_PAY_TM is null;
     END IF;
  END IF;
 
END;

ORA-04091: "table %s.%s is mutating, trigger/function may not see it"
Cause: A trigger (or a user defined plsql function that is referenced in this statement) attempted to look at (or modify) a table that was in the middle of being modified by the statement which fired it.
Action: Rewrite the trigger (or function) so it does not read that table.

if your trigger contains a select statement or an update statement referencing the table it is triggering off of you will receive the error.

 

這裡應該是

SELECT B.C_EDR_NO INTO v_row_id FROM acdep.GPPAYMENTFUND  A,acdep.T_FIN_CAVDOC B
            WHERE A.FUNDAPPLYNO=B.C_CAV_NO AND A.FUNDAPPLYNO=:NEW.FUNDAPPLYNO AND SUBSTR(B.C_EDR_NO,0,2)='03';
不允許GPPAYMENTFUND 進行SELECT 因為它是觸發器表