以前项目中编写的触发器
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;