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

忘是亡心i 2022-06-17 00:57 326阅读 0赞

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