trigger练习

declare
   v_trigger_user varchar2(100);
   v_trigger_date date;
   cursor c_switch(cp_switch tr_switch.switch_for%type) is
      select status from tr_switch
       where upper(trigger_name) = 'PREM_INFO_TAX_BI'
         and upper(switch_for) = upper(cp_switch);
   v_status tr_switch.status%type;
   v_seq_no  number;
   v_sqlcode  varchar2(6);
   v_sqlerrm  varchar2(200);
   v_error_comment varchar2(300);
begin
   v_error_comment:='before get_user';
   v_trigger_user := pub_package.get_user();
   v_trigger_date := sysdate;
   v_error_comment:='before LBS_SYNCH';
   open c_switch('LBS_SYNCH');
   fetch c_switch into v_status;
   if c_switch%FOUND and v_status = '1' then
      :new.created_by   := v_trigger_user;
      :new.created_date := v_trigger_date;
      :new.updated_by   := v_trigger_user;
      :new.updated_date := v_trigger_date;
   end if;
   close c_switch;
   exception
      when others then
      v_sqlcode      :=sqlcode;
      v_sqlerrm      :=substr(sqlerrm,1,200);
      insert into tr_error_log
                (error_no          ,
                 error_message     ,
                 trigger_name      ,
                 trigger_user      ,
                 trigger_date      ,
                 error_comment
                 )
                 values
                (v_sqlcode,
                 v_sqlerrm,
                 'PREM_INFO_TAX_BI',
                 v_trigger_user,
                 v_trigger_date,
                 v_error_comment
                 );
end;