触发器基本语法

一、触发器的语法如下

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 , sysdat