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_J DF_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 ROW
BEGIN
     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在实现触发器的时候有内部的一套机制，需要深入研究才行，目前开发阶段不涉及，以后有时间再研究吧。