? ?第一次写触发器，就有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的问题，直接搜索问题编号就好了。

? ?结果如下：代码涉及到对关联表的数据操作，比如查询关联表的总记录数或者往关联表中插入一条记