oracle 监控表数据变更触发器（脚本生成）

-------------- 构造insert action脚本---------------- 
select 1,REPLACE( 'CREATE OR REPLACE TRIGGER TR_TB_TEST_LOG
  AFTER INSERT OR UPDATE ON TB_TEST
  FOR EACH ROW
DECLARE
  LOG NUMBER(1)~~~~
BEGIN
   if inserting  then
   insert into tb_log(LOG_ID, OBJECT_NAME,KEY_VALUE, EMP_GCODE, EMP_NAME, ACTION_TYPE,UNDO_SQL,REDO_SQL)
        values(SQ_LOG.NEXTVAL,''TB_TEST'',:new.PROVINCE_CODE, 
         :new.OP_EMP_GCODE,:new.OP_EMP_NAME, ''新增'',
      ''delete TB_TEST where PROVINCE_CODE = ''''''||:new.PROVINCE_CODE||'''''';'',
       ''insert into TB_TEST('||wm_concat(a.column_name)||') 
values('||
       wm_concat(decode(b.data_type,'VARCHAR2','''''''||','CHAR','''''''||',
                 'NUMBER','''||NVL(TO_CHAR(','DATE','TO_DATE(''''''||TO_CHAR(','')||':new.'
        ||a.column_name||
       decode(b.data_type,'VARCHAR2','||''''''','CHAR','||''''''','NUMBER','),''NULL'')||''',
          'DATE',',''YYYY-MM-DD HH24:MI:SS'')||'''''',''''YYYY-MM-DD HH24:MI:SS'''')',''))||');''
          ); else ','~~~~',';') sql_text
          
from user_col_comments a , (select * from user_tab_columns order by column_id) b 
where a.table_name = b.table_name
 and a.column_name = b.column_name
 and a.table_name='TB_TEST'
 union all
---构造update action脚本
select 2, replace('
select need_log into log from TB_COLUMNS where object_name=''TB_TEST'' and column_name='''||a.column_name||''' ~~~~
if nvl(:old.'||a.column_name||','||decode(b.data_type,'VARCHAR2',''' ''','CHAR',''' ''',
     'NUMBER','-9999','DATE','SYSDATE',''' ''')||') <> nvl(:new.'||a.column_name||','||
     decode(b.data_type,'VARCHAR2',''' ''','CHAR',''' ''',
     'NUMBER','-9999','DATE','SYSDATE',''' ''')||') and log=1 then
     insert into tb_log(LOG_ID, OBJECT_NAME,KEY_VALUE,COLUMN_NAME,
            OLD_VALUE, NEW_VALUE, EMP_GCODE, EMP_NAME, ACTION_TYPE,UNDO_SQL, REDO_SQL)
        values(SQ_LOG.NEXTVAL, ''TB_TEST'',:old.PROVINCE_CODE,'''||a.column_name||
        ''',:old.'||a.column_name||
        ',:new.'||a.column_name||', 
        :new.OP_EMP_GCODE,:new.OP_EMP_NAME,''修改'', 
        ''update TB_TEST set '|| a.column_name
        ||' = 
        '||decode(b.data_type,'VARCHAR2','''''''||',
                                 'CHAR','''''''||',
                                 'NUMBER','''|| nvl(TO_CHAR(',
                                  'DATE','TO_DATE(''''''||TO_CHAR(','''''''||')
        ||':old.'||a.column_name||
        decode(b.data_type,'VARCHAR2','||''''''','CHAR','||''''''','NUMBER','),''null'')||''',
            'DATE',',''YYYY-MM-DD HH24:MI:SS'')||'''''',''''YYYY-MM-DD HH24:MI:SS'''')','||''''''')||'
          where PROVINCE_CODE=''''''||:new.PROVINCE_CODE' ||'||'''''''',
          ''update TB_TEST set '|| a.column_name
        ||' = 
        '||decode(b.data_type,'VARCHAR2','''''''||','CHAR','''''''||','NUMBER','''||nvl(TO_CHAR(',
            'DATE','TO_DATE(''''''||TO_CHAR(','''''''||')
        ||':new.'||a.column_name||decode(b.data_type,'VARCHAR2','||''''''','CHAR','||''''''','NUMBER','),''null'')||''',
            'DATE',',''YYYY-MM-DD HH24:MI:SS'')||'''''',''''YYYY-MM-DD HH24:MI:SS'''')','||''''''')||'
          where PROVINCE_CODE=''''''||:old.PROVINCE_CODE' ||'||'''''''') ~~~~
        
end if;','~~~~',';') AS C1
from user_col_comments a , (select * from user_tab_columns order by column_id) b 
where a.table_name = b.table_name
 and a.column_name = b.column_name
 and a.table_name='TB_TEST'
union all
SELECT 3, REPLACE( '  END IF~~~~ 
END~~~~ ','~~~~',';') FROM DUAL 

order by 1 asc

--------------------替换TB_TEST 为所要监控的表名，执行以上脚本，将获得的脚本执行后将生成监控触发器