oracle数据库高级应用之《触发器的建立》
(一)oracle数据库触发器的建立
eg1
CREATE OR REPLACE TRIGGER TRIGGER_ON_TD_DEPARTMENT AFTER INSERT OR UPDATE OR DELETE ON TD_DEPARTMENT REFERENCING OLD AS old NEW AS new FOR EACH ROW declare v_opttype varchar2(8); BEGIN if inserting then v_opttype := 'ADD'; else v_opttype := 'MOD'; if UPPER(:new.valid_falg) = 'N' and UPPER(:old.valid_falg) = 'Y' then v_opttype := 'DEC'; end if; if UPPER(:new.valid_falg) = 'Y' and UPPER(:old.valid_falg) = 'N' then v_opttype := 'ADD'; end if; end if; insert into TD_DEPARTMENT_TRIGER(id, dep_id, dep_name, dep_sname, comp_id, super_id, valid_falg, start_date, end_date, xorder, dep_grade, dep_type, dep_proy,dep_city, optdate,rmk, qq_flag, qq_optdate, mail_flag,mail_optdate,hr_flag, hr_optdate,oa_flag, oa_optdate, rtx_flag, rtx_optdate,opt_type, opt_date) select :new.id,:new.dep_id,:new.dep_name,:new.dep_sname,:new.comp_id,:new.super_id,:new.valid_falg,:new.start_date,:new.end_date,:new.xorder,new.dep_grade,:new.dep_type, :new.dep_proy, :new.dep_city, :new.optdate, :new.rmk,'0',sysdate,'0',sysdate,'0',sysdate,'0',sysdate,'0',sysdate,v_opttype,sysdate from dual; END;