编写Oracle触发器的样板

Oracle触发器

create or replace trigger IMAGTRIG.TR_AU_IMAGE_FILE_INDEX
   after update ON IMAGE_FILE_INDEX
   referencing new as new old as old
   for each row
declare
   v_trigger_user varchar2(50);
   v_trigger_date date;
   v_trigger_name varchar2(30) := 'TR_AU_IMAGE_FILE_INDEX';

   cursor c_switch(cp_switch varchar2) is
      select status
        from IMAGE_TR_SWITCH
       where trigger_name = upper(v_trigger_name)
         and switch_for   = cp_switch;
   v_status         IMAGE_TR_SWITCH.status%type;
   v_sqlcode        varchar2(6);
   v_sqlerrm        varchar2(200);
   v_error_comment  varchar2(300);
   --序列号
   v_seq_no         varchar2(20);
begin
   v_error_comment := 'before get_user';
   --获取用户
   v_trigger_user  :=  pub_sys_package.get_user();
   v_trigger_date  :=  sysdate;

   open c_switch('COL_LOG');
   fetch c_switch into v_status;
   if c_switch%found and v_status='1' then
    select SEQ_IMAGE_INDEX_CHANGE_LOG_ID.nextval into v_seq_no from dual;
          v_error_comment:='column log BAR_CODE';
      if (NVL(:old.BAR_CODE,'~') <> NVL(:new.BAR_CODE,'~')) then
            --记录BAR_CODE变更字段信息
            insert into IMAGE_INDEX_CHANGE_LOG(LOG_ID,CHANGE_TYPE,TABLE_NAME,TABLE_PRIMARY_KEY,TRIGGER_NAME,OPERATE_USER,
            OPERATE_DATE,OTHER_OLD_VALUE,OTHER_NEW_VALUE,REMARK)
             values(v_seq_no,
                    '01',
                    'IMAGE_FILE_INDEX',
                    :new.BAR_CODE,
                    'TR_AU_IMAGE_FILE_INDEX',
                    v_trigger_user,
                    v_trigger_date,
                    'BAR_CODE='||:old.BAR_CODE,
                    'BAR_CODE='||:new.BAR_CODE,
                    'BAR_CODE');
      end if;
--#log_content#
   end if;

   --出错处理
   exception
      when others then
      v_sqlcode      :=sqlcode;
      v_sqlerrm      :=substr(sqlerrm,1,200);
      insert into IMAGE_TR_ERROR_LOG(
            ERROR_NO             --系统错误代码
            ,ERROR_MESSAGE       --系统错误信息
            ,TRIGGER_NAME        --出错的trigger
            ,TRIGGER_USER        --出错的用户
            ,TRIGGER_DATE        --出错的时间
            ,ERROR_COMMENT       --出错详细信息
       )
       values(v_sqlcode
             ,v_sqlerrm
             ,upper(v_trigger_name)
             ,v_trigger_user
             ,v_trigger_date
             ,v_error_comment
       );

end TR_AU_IMAGE_FILE_INDEX;