首页 > 代码库 > 触发器基本语法
触发器基本语法
一、触发器的语法如下
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;
触发器基本语法
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。