oracle-ORA:04091：触发器/函数不能读它

oracle--ORA:04091：触发器/函数不能读它

? ?第一次写触发器，就有bug了

? ?前台用ajax保存用户的修改和删除，这时需求要加个触发器，在修改和删除时修改其他表的字段，加完触发器，点保存，前台弹出js错误，以为是js写的有误，结果查了半天js发现是ajax返回是出错。

? ?后台则为：2012-02-28 13:05:11,960 WARN [http-8080-Processor23] [RequestProcessor] Unhandled Exception thrown: class org.springframework.jdbc.BadSqlGrammarException。

? ? 原来还是触发器有问题，用PL/SQL写的触发器，没有测试，最后不用程序单走触发器，测试了一下触发器，发现提示错误信息为：ora-04091:表TB_JDF_XC.....发生了变化，触发器/函数不能读它。

? ? 写的触发器如下：用PL/SQL developer写的触发器

? ??CREATE OR REPLACE TRIGGER TB_PER_POSITONLEVEL_TRI

AFTER INSERT OR UPDATE OF c_effectivedate,c_postionlevel,c_postioncluster OR DELETE ON TB_PER_POSITONLEVEL FOR EACH ROWBEGIN     IF inserting THEN        UPDATE tb_inf_employee t SET t.c_positionlevel = ( SELECT c_postionlevel FROM (SELECT a.c_postionlevel FROM TB_PER_POSITONLEVEl a,tb_inf_employee b WHERE a.c_empoid = b.c_employeeid AND  a.c_empoid = :NEW.c_empoid ORDER BY a.c_effectivedate DESC,a.c_operatetime DESC) WHERE ROWNUM =1 ),                                     t.c_positionorder = ( SELECT c_postioncluster FROM (SELECT a.c_postioncluster FROM TB_PER_POSITONLEVEl a,tb_inf_employee b WHERE a.c_empoid = b.c_employeeid AND  a.c_empoid = :NEW.c_empoid ORDER BY a.c_effectivedate DESC,a.c_operatetime DESC) WHERE ROWNUM =1 );     END IF;     IF deleting THEN        UPDATE tb_inf_employee t SET t.c_positionlevel = ( SELECT c_postionlevel FROM (SELECT a.c_postionlevel FROM TB_PER_POSITONLEVEl a,tb_inf_employee b WHERE a.c_empoid = b.c_employeeid AND  a.c_empoid = :OLD.c_empoid ORDER BY a.c_effectivedate DESC,a.c_operatetime DESC) WHERE ROWNUM =1 ),                                     t.c_positionorder = ( SELECT c_postioncluster FROM (SELECT a.c_postioncluster FROM TB_PER_POSITONLEVEl a,tb_inf_employee b WHERE a.c_empoid = b.c_employeeid AND  a.c_empoid = :OLD.c_empoid ORDER BY a.c_effectivedate DESC,a.c_operatetime DESC) WHERE ROWNUM =1 );     END IF;     IF UPDATing THEN        UPDATE tb_inf_employee t SET t.c_positionlevel = ( SELECT c_postionlevel FROM (SELECT a.c_postionlevel FROM TB_PER_POSITONLEVEl a,tb_inf_employee b WHERE a.c_empoid = b.c_employeeid AND  a.c_empoid = :OLD.c_empoid ORDER BY a.c_effectivedate DESC,a.c_operatetime DESC) WHERE ROWNUM =1 ),                                     t.c_positionorder = ( SELECT c_postioncluster FROM (SELECT a.c_postioncluster FROM TB_PER_POSITONLEVEl a,tb_inf_employee b WHERE a.c_empoid = b.c_employeeid AND  a.c_empoid = :OLD.c_empoid ORDER BY a.c_effectivedate DESC,a.c_operatetime DESC) WHERE ROWNUM =1 );     END IF;                               END TB_PER_POSITONLEVEL_TRI;

?

--手动 insert update delete测试

SELECT * FROM TB_PER_POSITONLEVEl r WHERE r.c_empoid = '853949' FOR UPDATE --853949

SELECT * FROM tb_inf_employee t WHERE t.c_employeeid = '853949'

? ?上网搜索了一天触发器，答案不给力，后来搜索ORA-04091有了眉目，以后oracle的问题，直接搜索问题编号就好了。

? ?结果如下：代码涉及到对关联表的数据操作，比如查询关联表的总记录数或者往关联表中插入一条记录，该类型代码只能在语句级触发器中使用，如果在行级触发器中使用，将会报ORA-04091错误。oracle的意思是要是涉及子查询说明你数据库设计上就有问题！

? ?那我又要用行级触发器咋么办呢？

方法一：新建了与orginfo结构完全相同的临时表tmp_orginfo，使得在orginfo上的触发器读写tmp_orginfo,再用tmp_orginfo上的触发器写回orginfo,解决ora-04091问题。

方法二：开启自治事务

???oracle行级触发器无法读取当前表的数据，为达到操作本表的效果建议开启自治事务
create or replace trigger TRI_ACTION_LOG

after insert on action_log
for each row
declare
PRAGMA AUTONOMOUS_TRANSACTION;
co integer;
begin
????select count(1) into co from action_log;
????:new.log_state:=co;
????insert into action_log(log_state)
????values(:New.log_state);
????commit;
end;
把子查询放到中间变量里面，并用自治事务修改如下：

? ?CREATE OR REPLACE TRIGGER TB_PER_POSITONLEVEL_TRI

AFTER INSERT OR UPDATE OF c_effectivedate,c_postionlevel,c_postioncluster OR DELETE ON TB_PER_POSITONLEVEL

FOR EACH ROW

DECLARE

position_effdate DATE;

position_level varchar2(64);

position_order varchar2(64);

position_oid varchar2(128);

cou NUMBER(3);

PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

? ? ?--SELECT COUNT(*) INTO cou FROM (SELECT a.c_oid FROM TB_PER_POSITONLEVEl a,tb_inf_employee b WHERE a.c_empoid = b.c_employeeid AND ?a.c_empoid = :NEW.c_empoid ORDER BY nvl(a.C_EFFECTIVEDATE,TO_DATE('1900/01/01 00:00:00', 'YYYY/MM/DD HH24:MI:SS') ) DESC,a.c_operatetime DESC);

? ? ?--INSERT INTO A_TEMP VALUES(cou);

? ? ?IF inserting THEN

? ? ? ? SELECT COUNT(*) INTO cou FROM (SELECT a.c_oid FROM TB_PER_POSITONLEVEl a,tb_inf_employee b WHERE a.c_empoid = b.c_employeeid AND ?a.c_empoid = :NEW.c_empoid ORDER BY nvl(a.C_EFFECTIVEDATE,TO_DATE('1900/01/01 00:00:00', 'YYYY/MM/DD HH24:MI:SS') ) DESC,a.c_operatetime DESC);

?

? ? ? ?-- INSERT INTO A_TEMP VALUES('行级别触发器after');

? ? ? ? --INSERT INTO A_TEMP VALUES(position_oid);

? ? ? ? --INSERT INTO A_TEMP VALUES( to_char(position_effdate,'fmdd month yyyy') );

? ? ? ? --INSERT INTO A_TEMP VALUES(position_level);

? ? ? ? --INSERT INTO A_TEMP VALUES(position_order);

? ? ? ? --INSERT INTO A_TEMP VALUES(cou);

? ? ? ? IF cou <> 0 THEN

? ? ? ? ? ?SELECT CASE WHEN :new.c_oid = c_oid THEN :NEW.c_postionlevel ELSE c_postionlevel END INTO position_level FROM (SELECT a.c_oid, a.c_postionlevel FROM TB_PER_POSITONLEVEl a,tb_inf_employee b WHERE a.c_empoid = b.c_employeeid AND ?a.c_empoid = :NEW.c_empoid ORDER BY nvl(a.C_EFFECTIVEDATE,TO_DATE('1900/01/01 00:00:00', 'YYYY/MM/DD HH24:MI:SS') ) DESC,a.c_operatetime DESC) ?WHERE ROWNUM =1 ;

? ? ? ? ? ?SELECT CASE WHEN :new.c_oid = c_oid THEN :NEW.c_effectivedate ELSE c_effectivedate END INTO position_effdate FROM (SELECT a.c_oid,a.c_effectivedate FROM TB_PER_POSITONLEVEl a,tb_inf_employee b WHERE a.c_empoid = b.c_employeeid AND ?a.c_empoid = :NEW.c_empoid ORDER BY nvl(a.C_EFFECTIVEDATE,TO_DATE('1900/01/01 00:00:00', 'YYYY/MM/DD HH24:MI:SS') ) DESC,a.c_operatetime DESC) WHERE ROWNUM =1 ;

? ? ? ? ? ?SELECT CASE WHEN :new.c_oid = c_oid THEN :new.c_postioncluster ELSE c_postioncluster END INTO position_order FROM (SELECT a.c_oid,a.c_postioncluster FROM TB_PER_POSITONLEVEl a,tb_inf_employee b WHERE a.c_empoid = b.c_employeeid AND ?a.c_empoid = :NEW.c_empoid ORDER BY nvl(a.C_EFFECTIVEDATE,TO_DATE('1900/01/01 00:00:00', 'YYYY/MM/DD HH24:MI:SS') ) DESC,a.c_operatetime DESC) WHERE ROWNUM =1 ;

? ? ? ? ? ?SELECT c_oid INTO position_oid FROM (SELECT a.c_oid FROM TB_PER_POSITONLEVEl a,tb_inf_employee b WHERE a.c_empoid = b.c_employeeid AND ?a.c_empoid = :NEW.c_empoid ORDER BY nvl(a.C_EFFECTIVEDATE,TO_DATE('1900/01/01 00:00:00', 'YYYY/MM/DD HH24:MI:SS') ) DESC,a.c_operatetime DESC) WHERE ROWNUM =1 ;

?

? ? ? ? ? ?IF position_effdate <= :NEW.c_effectivedate THEN

? ? ? ? ? ? ? --INSERT INTO A_TEMP VALUES('插入的是最新的');

? ? ? ? ? ? ? UPDATE tb_inf_employee t SET t.c_positionlevel = :NEW.c_postionlevel,

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? t.c_positionorder = :NEW.c_postioncluster WHERE t.c_employeeid = :NEW.C_EMPOID;

? ? ? ? ? ? END IF;

? ? ? ? ? ? ELSIF position_effdate > :NEW.c_effectivedate THEN

? ? ? ? ? ? ? --INSERT INTO A_TEMP VALUES('插入的不是最新的，没有影响');

? ? ? ? ? ? ? UPDATE tb_inf_employee t SET t.c_positionlevel = position_level,

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? t.c_positionorder = position_order WHERE t.c_employeeid = :NEW.C_EMPOID;

?

? ? ? ? ? ? END IF;

? ? ? ? END IF;

? ? ? ? IF cou = 0 THEN

? ? ? ? ? ?UPDATE tb_inf_employee t SET t.c_positionlevel = :NEW.c_postionlevel,

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? t.c_positionorder = :NEW.c_postioncluster WHERE t.c_employeeid = :NEW.C_EMPOID;

? ? ? ? END IF;

? ? ?IF deleting THEN

? ? ? ? SELECT COUNT(*) INTO cou FROM (SELECT a.c_oid FROM TB_PER_POSITONLEVEl a,tb_inf_employee b WHERE a.c_empoid = b.c_employeeid AND ?a.c_empoid = :OLD.c_empoid ORDER BY nvl(a.C_EFFECTIVEDATE,TO_DATE('1900/01/01 00:00:00', 'YYYY/MM/DD HH24:MI:SS') ) DESC,a.c_operatetime DESC);

?

? ? ? ?-- INSERT INTO A_TEMP VALUES('行级别触发器after');

? ? ? ? --INSERT INTO A_TEMP VALUES(position_oid);

? ? ? ?-- INSERT INTO A_TEMP VALUES( to_char(position_effdate,'fmdd month yyyy') );

? ? ? ?-- INSERT INTO A_TEMP VALUES(position_level);

? ? ? ? --INSERT INTO A_TEMP VALUES(position_order);

? ? ? -- ?IF(:OLD.c_oid == )

? ? ? ? IF cou <> 1 THEN

? ? ? ? SELECT c_postionlevel INTO position_level FROM (SELECT a.c_postionlevel FROM TB_PER_POSITONLEVEl a,tb_inf_employee b WHERE a.c_empoid = b.c_employeeid AND ?a.c_empoid = :OLD.c_empoid AND a.c_oid <> :old.c_oid ORDER BY nvl(a.C_EFFECTIVEDATE,TO_DATE('1900/01/01 00:00:00', 'YYYY/MM/DD HH24:MI:SS') ) DESC,a.c_operatetime DESC) WHERE ROWNUM =1 ;

? ? ? ? SELECT c_effectivedate INTO position_effdate FROM (SELECT a.c_effectivedate FROM TB_PER_POSITONLEVEl a,tb_inf_employee b WHERE a.c_empoid = b.c_employeeid AND ?a.c_empoid = :OLD.c_empoid AND a.c_oid <> :old.c_oid ORDER BY nvl(a.C_EFFECTIVEDATE,TO_DATE('1900/01/01 00:00:00', 'YYYY/MM/DD HH24:MI:SS') ) DESC,a.c_operatetime DESC) WHERE ROWNUM =1 ;

? ? ? ? SELECT c_postioncluster INTO position_order FROM (SELECT a.c_postioncluster FROM TB_PER_POSITONLEVEl a,tb_inf_employee b WHERE a.c_empoid = b.c_employeeid AND ?a.c_empoid = :OLD.c_empoid AND a.c_oid <> :old.c_oid ORDER BY nvl(a.C_EFFECTIVEDATE,TO_DATE('1900/01/01 00:00:00', 'YYYY/MM/DD HH24:MI:SS') ) DESC,a.c_operatetime DESC) WHERE ROWNUM =1 ;

? ? ? ? SELECT c_oid INTO position_oid FROM (SELECT a.c_oid FROM TB_PER_POSITONLEVEl a,tb_inf_employee b WHERE a.c_empoid = b.c_employeeid AND ?a.c_empoid = :OLD.c_empoid AND a.c_oid <> :old.c_oid ORDER BY nvl(a.C_EFFECTIVEDATE,TO_DATE('1900/01/01 00:00:00', 'YYYY/MM/DD HH24:MI:SS') ) DESC,a.c_operatetime DESC) WHERE ROWNUM =1 ;

? ? ? ? UPDATE tb_inf_employee t SET t.c_positionlevel = position_level,

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?t.c_positionorder = position_order WHERE t.c_employeeid = :OLD.C_EMPOID;

? ? ? ? END IF;

? ? ? ? IF cou = 1 THEN

? ? ? ? UPDATE tb_inf_employee t SET t.c_positionlevel = '',

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?t.c_positionorder = '' WHERE t.c_employeeid = :OLD.C_EMPOID;

? ? ? ? END IF;

? ? ?END IF;

? ? ?IF UPDATing THEN

? ? ? ? SELECT CASE WHEN :new.c_oid = c_oid THEN :NEW.c_postionlevel ELSE c_postionlevel END INTO position_level FROM (SELECT a.c_oid, a.c_postionlevel FROM TB_PER_POSITONLEVEl a,tb_inf_employee b WHERE a.c_empoid = b.c_employeeid AND ?a.c_empoid = :NEW.c_empoid ORDER BY nvl(a.C_EFFECTIVEDATE,TO_DATE('1900/01/01 00:00:00', 'YYYY/MM/DD HH24:MI:SS') ) DESC,a.c_operatetime DESC) ?WHERE ROWNUM =1 ;

? ? ? ? SELECT CASE WHEN :new.c_oid = c_oid THEN :NEW.c_effectivedate ELSE c_effectivedate END INTO position_effdate FROM (SELECT a.c_oid,a.c_effectivedate FROM TB_PER_POSITONLEVEl a,tb_inf_employee b WHERE a.c_empoid = b.c_employeeid AND ?a.c_empoid = :NEW.c_empoid ORDER BY nvl(a.C_EFFECTIVEDATE,TO_DATE('1900/01/01 00:00:00', 'YYYY/MM/DD HH24:MI:SS') ) DESC,a.c_operatetime DESC) WHERE ROWNUM =1 ;

? ? ? ? SELECT CASE WHEN :new.c_oid = c_oid THEN :new.c_postioncluster ELSE c_postioncluster END INTO position_order FROM (SELECT a.c_oid,a.c_postioncluster FROM TB_PER_POSITONLEVEl a,tb_inf_employee b WHERE a.c_empoid = b.c_employeeid AND ?a.c_empoid = :NEW.c_empoid ORDER BY nvl(a.C_EFFECTIVEDATE,TO_DATE('1900/01/01 00:00:00', 'YYYY/MM/DD HH24:MI:SS') ) DESC,a.c_operatetime DESC) WHERE ROWNUM =1 ;

? ? ? ? SELECT c_oid INTO position_oid FROM (SELECT a.c_oid FROM TB_PER_POSITONLEVEl a,tb_inf_employee b WHERE a.c_empoid = b.c_employeeid AND ?a.c_empoid = :NEW.c_empoid ORDER BY nvl(a.C_EFFECTIVEDATE,TO_DATE('1900/01/01 00:00:00', 'YYYY/MM/DD HH24:MI:SS') ) DESC,a.c_operatetime DESC) WHERE ROWNUM =1 ;

? ? ? ? --INSERT INTO A_TEMP VALUES('行级别触发器after');

? ? ? ? --INSERT INTO A_TEMP VALUES(position_oid);

? ? ? ?-- INSERT INTO A_TEMP VALUES( to_char(position_effdate,'fmdd month yyyy') );

? ? ? ?-- INSERT INTO A_TEMP VALUES(position_level);

? ? ? ?-- INSERT INTO A_TEMP VALUES(position_order);

? ? ? ? UPDATE tb_inf_employee t SET t.c_positionlevel = position_level,

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?t.c_positionorder = position_order WHERE t.c_employeeid = :NEW.C_EMPOID;

? ? ?END IF;

? ? COMMIT;

END TB_PER_POSITONLEVEL_TRI;

--测试

SELECT * FROM TB_PER_POSITONLEVEl r WHERE r.c_empoid = '853949' FOR UPDATE --853949

INSERT INTO TB_PER_POSITONLEVEl(c_oid,c_empoid,c_effectivedate) ?VALUES('22','853949',to_date('2012-02-10','yyyy-mm-dd'));

DELETE FROM TB_PER_POSITONLEVEl WHERE c_empoid = '853949' AND c_oid = '22'

SELECT t.c_positionlevel,t.c_positionorder FROM tb_inf_employee t WHERE t.c_employeeid = '853949'

UPDATE tb_inf_employee t SET t.c_positionlevel = '11',

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?t.c_positionorder = '11' WHERE t.c_employeeid = '853949';

SELECT a.c_postioncluster FROM TB_PER_POSITONLEVEl a,tb_inf_employee b WHERE a.c_empoid = b.c_employeeid AND ?a.c_empoid = '853949' ORDER BY nvl(a.C_EFFECTIVEDATE,TO_DATE('1900/01/01 00:00:00', 'YYYY/MM/DD HH24:MI:SS') ) DESC,a.c_operatetime DESC

SELECT a.c_postionlevel FROM TB_PER_POSITONLEVEl a,tb_inf_employee b WHERE a.c_empoid = b.c_employeeid AND ?a.c_empoid = '853949' ORDER BY nvl(a.C_EFFECTIVEDATE,TO_DATE('1900/01/01 00:00:00', 'YYYY/MM/DD HH24:MI:SS') ) DESC,a.c_operatetime DESC

SELECT * FROM A_TEMP

DELETE FROM a_temp WHERE 1 = 1 ?

? ? 结果发现这里的子查询的结果不包括已经修改的记录，就是说before和after在查询关联表的情况下是没有区别的，不知道是怎么个情况，就不包括正在crud的那条记录，去掉自治事务，则报错ora：04091，后来又加了一个额外的判断看把查询出来的结果和new、old比较，判断是否修改的是要查的记录。

SELECT CASE WHEN :new.c_oid = c_oid THEN :NEW.c_postionlevel ELSE c_postionlevel END INTO position_level FROM (SELECT a.c_oid, a.c_postionlevel FROM TB_PER_POSITONLEVEl a,tb_inf_employee b WHERE a.c_empoid = b.c_employeeid AND ?a.c_empoid = :NEW.c_empoid ORDER BY nvl(a.C_EFFECTIVEDATE,TO_DATE('1900/01/01 00:00:00', 'YYYY/MM/DD HH24:MI:SS') ) DESC,a.c_operatetime DESC) ?WHERE ROWNUM =1 ;

? ? 暂时能用了，不过应该写的很不规范。以后在研究吧。

? ? 问题分析：就目前的线索来说，在触发器中不让用关联表使用子查询，mutating table（估计是正在修改的那条记录没有提交呢！！），要是非要这么用，就用自治事务，这时要注意，无论before，after都是不包括正在crud的那条记录，这说明其实触发器的执行过程中after和before其实都没有实际的更新数据库，crud操作和触发器body是在同一个事务中的，after是（crud、触发器body commit的顺序），before是（触发器body、crud commit的顺序）。都是最后一起提交事务，所谓before和after是说在同一个事务中的先后顺序罢了。采用自治事务after应该能达到先crud、在子查询中应该能查到刚才crud的那条记录的，可是实际情况是没有那条记录，这说明oracle在实现触发器的时候有内部的一套机制，需要深入研究才行，目前开发阶段不涉及，以后有时间再研究吧。

查看更多 下一篇