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