oracle触发器使用心得

一、问题实例

       

存在表poleinfotable包含如下字段：

              TOTALPOLEID                        --------线路id，主键

              CIRCUITRYNAME                      --------线路名称                

              POLEID                             --------杆塔号

              CIRCUITRYTYPEPARTAL                --------线路周期

              UNITSTATUSPARTAL                   --------一般周期

              TESHUQUPARTA                       --------特殊周期

              MINCYCLE                           --------实际周期

要求：实际周期=线路周期、一般周期、特殊周期中的最小值，当线路周期、一般周期、特殊周期的值放生变化时，要重新计算实际周期的值；

解决思路：使用oracle触发器，当CIRCUITRYTYPEPARTAL、UNITSTATUSPARTAL、TESHUQUPARTA中任意值放生变化，则自动更新MINCYCLE；

触发器语句：
CREATE OR REPLACE TRIGGER POLEINFOTABLE_CYCLE 
BEFORE INSERT OR UPDATE OF CIRCUITRYTYPEPARTAL,TESHUQUPARTAL,UNITSTATUSPARTAL ON POLEINFOTABLE 
FOR EACH ROW 
BEGIN
    :new.mincycle :=least(:new.CIRCUITRYTYPEPARTAL,:new.TESHUQUPARTAL,:new.UNITSTATUSPARTAL);
END;

二、感悟心得

       事实上我在写这个触发器的时候遇到了很多问题诸如：
ORA-04091: 表 SXB.POLEINFOTABLE 发生了变化, 触发器/函数不能读它；
ORA-00060: 等待资源时检测到死锁；

• 错误: ORA-04084: 无法更改此触发器类型的 NEW 值

下面我们把上面的情况逐个分析下：

在分析问题先我们先简单的回顾下触发器的语法：       

 create [or replace] trigger tri_name                               -----创建或覆盖触发器

        [before|after|instead of] tir_event                         -----触发器触发的时机

        on table_name|view_name|user_name|db_name                   -----触发器作用在什么地方（表、视图、用户模式、数据库）

        [for each row] [when tri_condition]                         -----是否为行级触发器，触发的条件表达式是什么

        begin                                                       -----开始

            plsql_sentences;                                        -----触发器的主体

        end tri_name;                                               -----结束

       在拿到问题后，我们首先想到的是在CIRCUITRYTYPEPARTAL、UNITSTATUSPARTAL、TESHUQUPARTA三个字段完成插入或者更新操作之后才对MINCYCLE进行修改，于是我们很自然的在创建触发器的时候选择‘after’作为触发时机；

        方案一：

CREATE OR REPLACE TRIGGER POLEINFOTABLE_CYCLE 
AFTER INSERT OR UPDATE OF CIRCUITRYTYPEPARTAL,TESHUQUPARTAL,UNITSTATUSPARTAL ON POLEINFOTABLE 
FOR EACH ROW 
BEGIN
   update poleinfotable set MINCYCLE=least(CIRCUITRYTYPEPARTAL,UNITSTATUSPARTAL,TESHUQUPARTAL) ;
END;

       当我们对表poleinfotable进行修改后就会出现‘ORA-04091: 表 SXB.POLEINFOTABLE 发生了变化, 触发器/函数不能读它；’的错误；

       原因是在触发事件发生后表poleinfotable产生了一个事务A（修改字段CIRCUITRYTYPEPARTAL、UNITSTATUSPARTAL、TESHUQUPARTA），如果此时我们想修改表poleinfotable的MINCYCLE字段，就又需要产生一个新的事务B（修改字段MINCYCLE）；但是此时的事务A并没有被提交，我们又要产生事务B，这在oracle中是不合法的；

       oracle的数据一致性要求，在一个表的事务没有提交之前，无法对这个表产生新的事务；

       于是我们自然就想到了自定事务，既然触发事件的事务没有提交，我们就自己另外定义一个事务来执行update语句；

       方案二：

CREATE OR REPLACE TRIGGER POLEINFOTABLE_CYCLE 
AFTER INSERT OR UPDATE OF CIRCUITRYTYPEPARTAL,TESHUQUPARTAL,UNITSTATUSPARTAL ON POLEINFOTABLE 
FOR EACH ROW 
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   update poleinfotable set MINCYCLE=least(CIRCUITRYTYPEPARTAL,UNITSTATUSPARTAL,TESHUQUPARTAL) ;
COMMIT;
END;

       此时当我们修改poleinfotable表的字段后就得到了‘ORA-00060: 等待资源时检测到死锁；’的错误了；

       这里我个人分析的原因是虽然我们自定义了事务来执行update，但其本质仍然是对表poleinfotable进行操作，理论上我们没有违背数据一致的原则，但实际上我们貌似打了个‘擦边球’，oracle并没有通过；

        那么我们换个思路，在触发器中我们不再进行修改操纵而是转而执行存储过程，由存储过程执行修改的部分；

        方案三：

        创建存储过程

         

CREATE OR REPLACE PROCEDURE POLEINFOTABLE_CYCLE 
         (
           CIRCUITRYTYPEPARTAL IN NUMBER 
         , UNITSTATUSPARTAL IN NUMBER 
         , TESHUQUPARTAL IN NUMBER 
        ) 
         AS 
         BEGIN
         update poleinfotable set MINCYCLE=least(CIRCUITRYTYPEPARTAL,UNITSTATUSPARTAL,TESHUQUPARTAL);
         END POLEINFOTABLE_CYCLE;

         修改触发器

       

CREATE OR REPLACE TRIGGER POLEINFOTABLE_CYCLE 
AFTER INSERT OR UPDATE OF CIRCUITRYTYPEPARTAL,TESHUQUPARTAL,UNITSTATUSPARTAL ON POLEINFOTABLE 
FOR EACH ROW 
DECLARE
   CIRCUITRYTYPEPARTAL NUMBER;
   TESHUQUPARTAL       NUMBER;
   UNITSTATUSPARTAL    NUMBER;
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   CIRCUITRYTYPEPARTAL := :new.CIRCUITRYTYPEPARTAL;
   TESHUQUPARTAL := :new.TESHUQUPARTAL;
   UNITSTATUSPARTAL := :new.UNITSTATUSPARTAL;
COMMIT;
END;

       这样运行的结果仍然是死锁；

       此时我们分析下死锁的原因，之所以会出现这种情况是因为触发事件发生在数据修改之后，而触发结果和触发条件都作用在同一个表上，由于触发器的原因导致表的修改事务无法提交，而修改事务无法提交又导致触发器无法结束，这是一个死循环；

       那么如果我们让触发事件发生在数据修改之前呢？此时先产生事务B，而此时没有事务A，所以事务B可以正常执行，而当触发器结束后，事务B也就结束了，此时再进行数据修改，产生事务A，事务A也就可以提交了；于是就有了上文的语句：

CREATE OR REPLACE TRIGGER POLEINFOTABLE_CYCLE 
BEFORE INSERT OR UPDATE OF CIRCUITRYTYPEPARTAL,TESHUQUPARTAL,UNITSTATUSPARTAL ON POLEINFOTABLE 
FOR EACH ROW 
BEGIN
    :new.mincycle :=least(:new.CIRCUITRYTYPEPARTAL,:new.TESHUQUPARTAL,:new.UNITSTATUSPARTAL);
END;

      三、 总结

               （1）当触发条件和触发结果作用在一个表的时候，触发时机因该选before；

               （2）当触发条件在表A，触发结果在表B时，触发时机可以选after；

          需要注意的是：触发器中有两个列标识符‘:new.列’，‘:old.列’，只有在触发条件为‘before’时才可以对‘:new.列’进行赋值；在触发时机为’after‘时，只能对‘:new.列’，‘:old.列’进行引用，不能赋值，也不能修改；