oracle表变更记录脚本,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 为所要监控的表名，执行以上脚本，将获得的脚本执行后将生成监控触发器