create or replace trigger fa_bill
before insert on tmp_tab_fa
for each row
declarebill1 varchar2(20);
bill2 varchar2(20);
bill2_last4 varchar2(10);
thegroup_id varchar2(20);
short_num varchar2(20);v_count number;
-- local variables here
begin
bill1:=trim(:new.a);
bill2:=trim(:new.b);
thegroup_id:=trim(:new.c);
short_num:=trim(:new.d);--触发器执行前判断是否存在select count(*) into v_count from tmp_tab where to_char(input_time,'yyyymm')=to_char(sysdate,'yyyymm') and a=bill1;
if v_count=0 then insert into tmp_tab select bill1,bill2,thegroup_id,short_num,sysdate from dual;
end if;end fa_bill;
before insert on tmp_tab_fa
for each row
declarebill1 varchar2(20);
bill2 varchar2(20);
bill2_last4 varchar2(10);
thegroup_id varchar2(20);
short_num varchar2(20);v_count number;
-- local variables here
begin
bill1:=trim(:new.a);
bill2:=trim(:new.b);
thegroup_id:=trim(:new.c);
short_num:=trim(:new.d);--触发器执行前判断是否存在select count(*) into v_count from tmp_tab where to_char(input_time,'yyyymm')=to_char(sysdate,'yyyymm') and a=bill1;
if v_count=0 then insert into tmp_tab select bill1,bill2,thegroup_id,short_num,sysdate from dual;
end if;end fa_bill;
如果一定要控制的话可以在表tmp_tab中使用唯一约束,触发器中使用异常处理,不要让异常抛到外层.