触发器不能读它的问题

 http://space.itpub.net/7728585/viewspace-718992

 

 

 

报错如下：

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
        UPDATESET 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
        UPDATESET 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 因为它是触发器表