TRIG自动处理修改自己TRIG

create or replace procedure "MES_TABEL_LOG"
(tabel_name in  varchar2,creat_trigger in integer :=0)
is
PRAGMA AUTONOMOUS_TRANSACTION;--is owen pragram
  job binary_integer;
  i integer;
  m_sql varchar2(4000);
  m_sql_col varchar2(4000);
  m_sql_value varchar2(4000);
  m_sql_filter varchar2(4000);
  m_tem  varchar2(4000);
  m_col varchar2(200);
  m_col_type varchar2(50);
  m_col_len number;
  m_tab1 varchar2(200);
  m_tab2 varchar2(200);
  v_sqlcode  varchar2(100);
  v_sqlerrm  varchar2(200);
  cursor cur_col(tab1 varchar2,tab2 varchar2) is
    select COLUMN_NAME from all_tab_columns where TABLE_NAME = tab1
                 minus select COLUMN_NAME from all_tab_columns where TABLE_NAME = tab2;
  cursor cur_col_1(tab1 varchar2) is
    select COLUMN_NAME from all_tab_columns where TABLE_NAME = tab1 ORDER BY COLUMN_ID ;
begin
  m_tab1:=upper(tabel_name);
  m_tab2:=m_tab1||'_LOG';
  ----э
       select count(*) into i from all_tables where table_name=m_tab2;
       if i=0 then
                m_sql:='create table'|| m_tab2 ||'
                ( USERID      VARCHAR2(7),
                  COMMANDTYPE VARCHAR2(10),
                  DATETIME    DATE,
                  OS_USER     VARCHAR2(20) default SYS_CONTEXT(''USERENV'' ,''OS_USER''),
                  OS_IP       VARCHAR2(15) default SYS_CONTEXT(''USERENV'',''IP_ADDRESS''),
                  OS_PC       VARCHAR2(20) default SYS_CONTEXT(''USERENV'',''HOST''))';
               execute immediate m_sql;
               insert into MES_trig_log (Table_Name,Trans_Date,Errcode,Errm)
                      values(m_tab2,sysdate,'create TABLE',m_sql);
               commit;
       end if;
       open cur_col(m_tab1,m_tab2);
           loop
              fetch cur_col into m_col;
              exit when cur_col%notfound;
              select t.DATA_TYPE,t.DATA_LENGTH into m_col_type,m_col_len from all_tab_columns t where t.TABLE_NAME=m_tab1 and t.COLUMN_NAME=m_col;
              m_sql:='ALTER TABLE '||m_tab2 ||' ADD ' ||m_col ||' '|| m_col_type;
              IF m_col_type='VARCHAR2' then
                 m_sql:=m_sql||'('||m_col_len||')';
              elsif m_col_type='CHAR' then
                 m_sql:=m_sql||'('||m_col_len||')';
              end if;
              BEGIN
                  execute immediate m_sql;
                  insert into MES_trig_log (Table_Name,Trans_Date,Errcode,Errm)
                      values(m_tab2,sysdate,'alter table',m_sql);
                  commit;
              END;
           end loop;
       close cur_col;
  if creat_trigger=0 then---create job
       m_sql:='MES_TABEL_LOG('''||m_tab1 ||''',true);';
       select count(*) into i from all_jobs where what=m_sql;
       if i =0 then
         sys.dbms_job.submit(job => job,
                    what => m_sql,
                    next_date => sysdate+0.0007);---delay 1min run
        commit;
        end if;
  elsif  creat_trigger=1 then---эtrig
  if m_tab1='EQMS' then
     m_sql_filter:='update of "MACH_ID","MACH_DESC","WORK_CTR","FAC_ID","PN_CHK","FAB_INLINE","DELL_LINE","CHECK_HOLD_FLAG","WARN_MSG","HOLD_TIME","PROCESS_TYPE","R_CHECK_HOLD_FLAG","R_WARN_MSG","R_HOLD_TIME","AFTER_NUM","SHIFT_TIME","SCH_QTY" ';
  else
      m_sql_filter:=' update ';
  end if;
m_sql:='create or replace trigger '||m_tab2||'_TRI after insert or
'||m_sql_filter||'
 or delete on CPT.'||m_tab1||' for each row
declare i integer;v_userid varchar2(7);v_tab varchar2(50); v_command varchar2(10);v_sqlcode varchar2(100);v_sqlerrm varchar2(2000);
------trig create time '||to_char(sysdate,'yyyy/mm/dd hh24:mi:ss')||'
begin v_tab:='''||m_tab1||'''; -----TABEL CHANGE
   begin----GET USER ID
          select userid into v_userid from usersessionmap where sessionid=sys.dbms_session.unique_session_id;
      exception when others then v_userid:=SYS_CONTEXT(''USERENV'',''SESSION_USERID'');
   end;
   SELECT COUNT(*) INTO i FROM (select COLUMN_NAME from all_tab_columns where TABLE_NAME = v_tab minus select COLUMN_NAME from all_tab_columns where TABLE_NAME = v_tab'||chr(124)||chr(124)||chr(39)||'_LOG'');
   if i>0 then MES_TABEL_LOG(v_tab); end if;
   if inserting then v_command:=''insert''; elsif updating then v_command:=''update''; else v_command:=''delete''; end if;
  ------WRITE DB
   if inserting then ';
m_sql_col:='
     insert into '||m_tab2||' (';
--m_sql_old:=m_sql_new;
i:=0;
       open cur_col_1(m_tab1);
           loop
              fetch cur_col_1 into m_col;
              exit when cur_col_1%notfound;
              i:=i+1;
              m_sql_col:=m_sql_col||m_col||',';
              m_sql_value:=m_sql_value||':new.'||m_col||',';
              if i=5 then
                m_sql_col:=m_sql_col||'
                     ';
                m_sql_value:=m_sql_value||'
                     ';
                i:=0;
              end if;
           end loop;
       close cur_col_1;
m_sql_col:=m_sql_col||'userid,datetime,COMMANDTYPE )
       values('||m_sql_value||'v_userid,sysdate,v_command);
';
m_tem:='  else'|| replace(m_sql_col,':new.',':old.')||' end if;
exception when others then v_sqlcode:=sqlcode; v_sqlerrm:=sqlerrm;
   insert into MES_trig_log (Table_Name,Trans_Date,Errcode,Errm,USERID) values(v_tab,sysdate,v_sqlcode,v_sqlerrm,v_userid);
end '||m_tab1||'_LOG_TRI;';
--execute immediate 'grant create trigger to CPT';
execute immediate m_sql||m_sql_col||m_tem;
commit;
insert into MES_trig_log (Table_Name,Trans_Date,Errcode,Errm)
                      values(m_tab2,sysdate,'эTRIGGER',''); --write log
commit;
 end if;
    EXCEPTION
  when no_data_found then
       null;
  WHEN others THEN
      v_sqlcode:=sqlcode;
      v_sqlerrm:=sqlerrm;
      insert into MES_trig_log (Table_Name,Trans_Date,Errcode,Errm) values(m_tab2,sysdate,v_sqlcode,v_sqlerrm);---write error log
      commit;
end MES_TABEL_LOG;