ITPub博客

首页 > Linux操作系统 > Linux操作系统 > ORA-04091 触发器/函数不能读

ORA-04091 触发器/函数不能读

原创 Linux操作系统 作者:gaopengtttt 时间:2012-03-19 12:12:45 0 删除 编辑

 

报错如下:

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


 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7728585/viewspace-718992/,如需转载,请注明出处,否则将追究法律责任。

上一篇: oracle 直方图
请登录后发表评论 登录
全部评论
wxh gp_22389860 <<深入理解MySQL主从原理专栏>> 发布 可加WX了解

注册时间:2008-10-13

  • 博文量
    700
  • 访问量
    2970665