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