数据库触发器

CREATE OR REPLACE TRIGGER cux_ahl_visits_imp_trg--触发器的名称  

      AFTER UPDATE OR  INSERT OR DELETE ON ahl_visits_b--被监控的表（before）  

      FOR EACH ROW

DECLARE  

      CURSOR cur_data IS    

               SELECT avb.visit_id。。。。。。。--数据获取      

                FROM ahl_visits_b avb。。。。      

                WHERE 1 = 1。。。      

                AND avb.visit_id = :new.visit_id;  

    --PRAGMA AUTONOMOUS_TRANSACTION;--自助事务处理

BEGIN  

  IF updating THEN---更新动作时

      /*     监控ahl_visits_b表的status_code字段，当状态改变为CLOSED时，触发器触发，插入数据到表cux_mro_tbsinfo_summary   */  

     IF :old.status_code <> 'CLOSED' AND :new.status_code = 'CLOSED' THEN    

           FOR rec_data IN cur_data LOOP

                   update cux_mro_tbsinfo_summary。。。。。。。;        

            END LOOP; 

    END IF;

ELSIF inserting THEN--插入操作时

     FOR rec_data IN cur_data LOOP

                   INSERT INTO cux_mro_tbsinfo_summary(。。。。。)VALUES(。。。。。。。。。);        

     END LOOP;

END IF;

EXCEPTION

  WHEN OTHERS THEN 

    ---触发器报错时抛出异常

    app_exception.raise_exception(exception_type => 'CUX',

                                  exception_code => 21111,

                                  exception_text => 'Unexpected error in trigger XXIV_RCV_TRANS_AIR_TRG:' ||

                                                    substr(SQLERRM,

                                                           1,

                                                           1900));

END

 

转载于:https://www.cnblogs.com/wang-chen/p/4926409.html