首页 > 代码库 > 触发器基本语法

触发器基本语法

一、触发器的语法如下

CREATE OR REPLACE TRIGGER trigger_name<before | after | instead of> <insert | update | delete> ON table_name
[FOR EACH ROW]
WHEN (condition)
DECLARE
BEGIN
--触发器代码
END;
Trigger_name是触发器的名称。<before | after | instead of>可以选择before或者after或instead of。
Before表示在DML语句实施前执行触发器,而after表示在在dml语句实施之后执行触发器,instead of触发器用在对视图的更新上。
<insert | update | delete>可以选择一个或多个DML语句,如果选择多个,则用or分开,如:insert or update。
Table_name是触发器关联的表名。
[FOR EACH ROW]为可选项,如果注明了FOR EACH ROW,则说明了该触发器是一个行级的触发器,DML语句处理每条记录都会执行触发器;
否则是一个语句级的触发器,每个DML语句触发一次。
WHEN后跟的condition是触发器的响应条件,只对行级触发器有效,当操作的记录满足condition时,触发器才被执行,否则不执行。
Condition中可以通过new对象和old对象(注意区别于前面的:new和:old,在代码中引用需要加上冒号)来引用操作的记录。

二、实例

create or replace trigger tri_gend_det_sync  after insert or update or delete on gend_det
for each row
declare  var_hz_count number(9);
         var_hn_count number(9);
         var_wj_count number(9);
         pragma autonomous_transaction;
begin
    ---删除数据做操作
    if deleting then
    /*  delete from hzktc.gend_det@hzerp
       where gend_gen = :old.gend_gen
             and gend_option = :old.gend_option;*/
             
      delete from huinan.gend_det@huinanerp
       where gend_gen = :old.gend_gen
             and gend_option = (case when :old.gend_gen =‘RD_PRO_ID‘ then :old.gend_property1 else   :old.gend_option end)
             and :old.gend_synchro = 1; --suyanjiang 2016-9-18
         
         delete from aid_det
        where exists(select ai_type  from ai_mstr join aid_det on ai_type = aid_type 
                     where  aid_sync = 1
                       and aid_type =‘6‘)
          and aid_code = :old.gend_option
          and :old.gend_synchro = 1; --suyanjiang 2016-9-18
      
      if :new.gend_gen=‘ERROR_CODE‘ then
        delete from szfile.gend_det@filedata
          where gend_gen = ‘MISS_CODE‘
          and gend_option = :old.gend_option; 
      end if;     
      
      delete from dbuser.gend_det@dberp
       where gend_gen = :old.gend_gen
             and gend_option = :old.gend_option ;        
    end if;
    ---插入数据做操作
    if inserting  then
     
      if :new.gend_gen =‘RD_PRO_ID‘ then 
        insert into aid_det(aid_type, aid_code, aid_name, aid_valid, aid_sync, aid_rmks,
            aid_crt_by, aid_crt_date, aid_mod_by, aid_mod_date)
        select ‘6‘, :new.gend_option ,:new.gend_name, (case :new.gend_disabled when 0 then 1 else 0 end), 1, ‘‘,
            :new.gend_crt_by , sysdate, :new.gend_crt_by, sysdate
        from dual
        where  not exists(select * from aid_det where aid_type = 6 and aid_code = :new.gend_option ); 
     end if; 
    
     /* var_hz_count   := 0;
      select count(*) into var_hz_count
      from hzktc.gend_det@hzerp
      where gend_gen = :new.gend_gen   and 
            gend_option = :new.gend_option;
       
      
      if var_hz_count =0 then 
          insert into hzktc.gend_det@hzerp(gend_gen,gend_option,gend_name,gend_crt_by,gend_crt_date,gend_property1,gend_property2,gend_property3,
               gend_property4,gend_disabled,gend_char1,gend_char2,gend_char3,gend_char4,gend_char5,gend_char6,gend_qty1,gend_qty2,gend_property5,gend_property6)
           values(:new.gend_gen,:new.gend_option,:new.gend_name,:new.gend_crt_by,:new.gend_crt_date,:new.gend_property1,:new.gend_property2,
                 (case  when :new.gend_gen  =‘SRC_CAT‘ then  ‘‘ else  :new.gend_property3  end),
           :new.gend_property4,:new.gend_disabled,:new.gend_char1,:new.gend_char2,:new.gend_char3,:new.gend_char4,:new.gend_char5,:new.gend_char6,:new.gend_qty1,:new.gend_qty2,
            :new.gend_property5,:new.gend_property6);
     end if;*/
      
      var_hn_count   := 0;
      select count(*) into var_hn_count
      from huinan.gend_det@huinanerp
      where gend_gen = :new.gend_gen   and 
            gend_option = (case when :new.gend_gen =‘RD_PRO_ID‘ then :new.gend_property1 else   :new.gend_option end)
            and :new.gend_synchro = 1; --suyanjiang 2016-9-18
       
      
      if var_hn_count =0 and :new.gend_gen = ‘RD_PRO_ID‘ and :new.gend_property1 is not null and :new.gend_synchro = 1 then  
          insert into huinan.gend_det@huinanerp(gend_gen,gend_option,gend_name,gend_crt_by,gend_crt_date,gend_property1,gend_property2,gend_property3,
                 gend_property4,gend_disabled,gend_char1,gend_char2,gend_char3,gend_char4,gend_char5,gend_char6,gend_qty1,gend_qty2,gend_property5,gend_property6,
                 gend_property7,gend_property8,gend_property9,gend_property10,gend_property11,gend_property12)
           values(:new.gend_gen,:new.gend_property1,:new.gend_name,:new.gend_crt_by,:new.gend_crt_date,‘‘,:new.gend_property2,
             (case  when :new.gend_gen  =‘SRC_CAT‘ then  ‘‘ else  :new.gend_property3  end),
             :new.gend_property4,:new.gend_disabled,:new.gend_char1,:new.gend_char2,:new.gend_char3,:new.gend_char4,:new.gend_char5,:new.gend_char6,:new.gend_qty1,:new.gend_qty2,
             :new.gend_property5,:new.gend_property6,:new.gend_property7,:new.gend_property8,:new.gend_property9,:new.gend_property10,:new.gend_property11,:new.gend_property12);  
      
      elsif var_hn_count =0 and :new.gend_gen <>  ‘RD_PRO_ID‘ and :new.gend_synchro = 1 then  
           insert into huinan.gend_det@huinanerp(gend_gen,gend_option,gend_name,gend_crt_by,gend_crt_date,gend_property1,gend_property2,gend_property3,
                 gend_property4,gend_disabled,gend_char1,gend_char2,gend_char3,gend_char4,gend_char5,gend_char6,gend_qty1,gend_qty2,gend_property5,gend_property6,
                 gend_property7,gend_property8,gend_property9,gend_property10,gend_property11,gend_property12)
           values(:new.gend_gen,:new.gend_option,:new.gend_name,:new.gend_crt_by,:new.gend_crt_date,:new.gend_property1,:new.gend_property2,
             (case  when :new.gend_gen  =‘SRC_CAT‘ then  ‘‘ else  :new.gend_property3  end),
             :new.gend_property4,:new.gend_disabled,:new.gend_char1,:new.gend_char2,:new.gend_char3,:new.gend_char4,:new.gend_char5,:new.gend_char6,:new.gend_qty1,:new.gend_qty2,
             :new.gend_property5,:new.gend_property6,:new.gend_property7,:new.gend_property8,:new.gend_property9,:new.gend_property10,:new.gend_property11,:new.gend_property12);
      end if;
      
      if :new.gend_gen=‘ERROR_CODE‘ then
        var_wj_count   := 0;
        select count(*) into var_wj_count
        from szfile.gend_det@filedata
        where gend_gen = ‘MISS_CODE‘   and 
              gend_option = :new.gend_option;
         
        
        if var_wj_count =0 then  
             insert into szfile.gend_det@filedata(gend_gen,gend_option,gend_name,gend_crt_by,gend_crt_date,gend_property1,gend_property2,gend_property3,
                   gend_property4,gend_disabled,gend_char1,gend_char2,gend_char3,gend_char4,gend_char5,gend_char6,gend_qty1,gend_qty2)
             values(‘MISS_CODE‘,:new.gend_option,:new.gend_name,:new.gend_crt_by,:new.gend_crt_date,:new.gend_property1,:new.gend_property2,:new.gend_property3 ,
               :new.gend_property4,:new.gend_disabled,:new.gend_char1,:new.gend_char2,:new.gend_char3,:new.gend_char4,:new.gend_char5,:new.gend_char6,:new.gend_qty1,:new.gend_qty2);
        end if; 
      end if;
      
      select count(*) into var_wj_count
      from dbuser.gend_det@dberp
      where gend_gen=:new.gend_gen
        and gend_option=:new.gend_option;
      
      if var_wj_count=0 and :new.gend_gen  in (‘SBGD_SCHEME‘,‘DB_PART‘) then
        insert into dbuser.gend_det@dberp(GEND_GEN,GEND_OPTION,GEND_NAME,GEND_PROPERTY1,GEND_PROPERTY2,GEND_PROPERTY3,GEND_PROPERTY4,
                                          GEND_DISABLED,GEND_CHAR1,GEND_CHAR2,GEND_CHAR3,GEND_CHAR4,GEND_CHAR5,GEND_CHAR6,
                                          GEND_QTY1,GEND_QTY2,GEND_PROPERTY5,GEND_PROPERTY6)
        values(:new.gend_gen,:new.gend_option,:new.gend_name,:new.gend_property1,:new.gend_property2,:new.gend_property3,:new.gend_property4,
               :new.gend_disabled,:new.gend_char1,:new.gend_char2,:new.gend_char3,:new.gend_char4,:new.gend_char5,:new.gend_char6,
               :new.gend_qty1,:new.gend_qty2, :new.gend_property5,:new.gend_property6);                                                                
      end if;       
    end if;
    if updating then
          update dbuser.gend_det@dberp  set
                                     gend_name       = :new.gend_name, 
                                     gend_crt_by     = :new.gend_crt_by,
                                     gend_crt_date   = :new.gend_crt_date,
                                     gend_property1  = :new.gend_property1,
                                     gend_property2  = :new.gend_property2,
                                     gend_property3  = :new.gend_property3,
                                     gend_property4  = :new.gend_property4,
                                     gend_disabled   = :new.gend_disabled,
                                     gend_char1      = :new.gend_char1,
                                     gend_char2      = :new.gend_char2,
                                     gend_char3      = :new.gend_char3,
                                     gend_char4      = :new.gend_char4,
                                     gend_char5      = :new.gend_char5,
                                     gend_char6      = :new.gend_char6,
                                     gend_qty1       = :new.gend_qty1,
                                     gend_qty2       = :new.gend_qty2,
                                     gend_property5  = :new.gend_property5,
                                     gend_property6  = :new.gend_property6
                        where gend_gen  = :new.gend_gen and
                              gend_option = :new.gend_option;
                              
           update huinan.gend_det@huinanerp   set
                                     gend_name       = :new.gend_name, 
                                     gend_crt_by     = :new.gend_crt_by,
                                     gend_crt_date   = :new.gend_crt_date,
                                     gend_property1  = :new.gend_property1,
                                     gend_property2  = :new.gend_property2,
                                     gend_property3  =  (case when :new.gend_gen  =‘SRC_CAT‘ then  ‘‘ else  :new.gend_property3  end),
                                     gend_property4  = :new.gend_property4,
                                     gend_disabled   = :new.gend_disabled,
                                     gend_char1      = :new.gend_char1,
                                     gend_char2      = :new.gend_char2,
                                     gend_char3      = :new.gend_char3,
                                     gend_char4      = :new.gend_char4,
                                     gend_char5      = :new.gend_char5,
                                     gend_char6      = :new.gend_char6,
                                     gend_qty1       = :new.gend_qty1,
                                     gend_qty2       = :new.gend_qty2,
                                     gend_property5  = :new.gend_property5,
                                     gend_property6  = :new.gend_property6,
                                     gend_property7  = :new.gend_property7,
                                     gend_property8  = :new.gend_property8,
                                     gend_property9  = :new.gend_property9,
                                     gend_property10  = :new.gend_property10,
                                     gend_property11  = :new.gend_property11,
                                     gend_property12  = :new.gend_property12
                        where gend_gen  = :new.gend_gen and
                              gend_option =( case when :new.gend_gen =‘RD_PRO_ID‘ then :new.gend_property1 else :new.gend_option end)
                              and :new.gend_synchro = 1;

         if :new.gend_gen=‘ERROR_CODE‘ then                      
           update szfile.gend_det@filedata   set
                                     gend_name       = :new.gend_name, 
                                     gend_crt_by     = :new.gend_crt_by,
                                     gend_crt_date   = :new.gend_crt_date,
                                     gend_property1  = :new.gend_property1,
                                     gend_property2  = :new.gend_property2,
                                     gend_property3  = :new.gend_property3,
                                     gend_property4  = :new.gend_property4,
                                     gend_disabled   = :new.gend_disabled,
                                     gend_char1      = :new.gend_char1,
                                     gend_char2      = :new.gend_char2,
                                     gend_char3      = :new.gend_char3,
                                     gend_char4      = :new.gend_char4,
                                     gend_char5      = :new.gend_char5,
                                     gend_char6      = :new.gend_char6,
                                     gend_qty1       = :new.gend_qty1,
                                     gend_qty2       = :new.gend_qty2
                        where gend_gen  = ‘MISS_CODE‘ and
                              gend_option = :new.gend_option; 
         end if;                                                    
     end if;


 commit;

end tri_gend_det_sync;

  

触发器基本语法