触发器编写

以前项目中编写的触发器

 

create or replace trigger TRG_IUD_EX_FEE_SECHEME_MAIN
  after insert or update or delete
  on fee_scheme_main 
  for each row
declare
  emp_table_name varchar2(20) default 'fee_scheme_main';
  emp_syn_status varchar2(1) default '0';
  emp_dm_type varchar2(10);
  v_count number;
begin
   if inserting then
       emp_dm_type := 'insert';
       insert into t_logs_activity_fee(table_name,corp_code,syn_status,dm_type)
       values(emp_table_name,:New.scheme_id,emp_syn_status,emp_dm_type);
      
   end if;
  if updating then
       select count(*) into v_count  from t_logs_activity_fee t where t.corp_code=:old.scheme_id
       and t.table_name=emp_table_name;

       if v_count>0 then
       emp_dm_type := 'update';
       update t_logs_activity_fee t set t.syn_status=emp_syn_status,t.dm_type=emp_dm_type where t.corp_code=:New.scheme_id
       and t.table_name=emp_table_name;
       else
       emp_dm_type := 'insert';
        insert into t_logs_activity_fee(table_name,corp_code,syn_status,dm_type)
       values(emp_table_name,:New.scheme_id,emp_syn_status,emp_dm_type);
       end if;
       end if;
  
  if deleting then
       emp_dm_type := 'delete';
       select count(*) into v_count  from t_logs_activity_fee t where t.corp_code=:old.scheme_id
       and t.table_name=emp_table_name;
       if v_count>0 then
       update t_logs_activity_fee t set t.syn_status=emp_syn_status,t.dm_type=emp_dm_type where t.corp_code=:old.scheme_id
       and t.table_name=emp_table_name;
       else
       insert into t_logs_activity_fee(table_name,corp_code,syn_status,dm_type)
       values(emp_table_name,:old.scheme_id,emp_syn_status,emp_dm_type);
       end if;

       end if;

end TRG_IUD_EX_FEE_SECHEME_MAIN;