觸發器基本文法

標籤：action   comm   creat   disabled   sysdate   行觸發器   select   not   資料   

一、觸發器的文法如下

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_detfor each rowdeclare  var_hz_count number(9);         var_hn_count number(9);         var_wj_count number(9);         pragma autonomous_transaction;begin    ---刪除資料做操作    if deleting then    /*  delete from [email protected]       where gend_gen = :old.gend_gen             and gend_option = :old.gend_option;*/                   delete from [email protected]       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 [email protected]          where gend_gen = ‘MISS_CODE‘          and gend_option = :old.gend_option;       end if;                 delete from [email protected]       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 [email protected]      where gend_gen = :new.gend_gen   and             gend_option = :new.gend_option;                   if var_hz_count =0 then           insert into [email protected](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 [email protected]      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 [email protected](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 [email protected](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 [email protected]        where gend_gen = ‘MISS_CODE‘   and               gend_option = :new.gend_option;                         if var_wj_count =0 then               insert into [email protected](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 [email protected]      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 [email protected](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 [email protected]  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 [email protected]   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 [email protected]   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;

　　

觸發器基本文法