Blog do Marcio Portes

Certa vez, em minha carreira, me deparei com esse problema: necessidade de fazer auditoria em um esquema. Naquela época fiz uma pesquisa para evitar codificar e não encontrei nada built in, então encontrei esse exemplo abaixo na asktom e funcionou perfeitamente bem.

O problema era simples, saber quando, quem, qual tabela, qual campo, valor antigo e valor novo no esquema inteiro. Fácil não!

Primeiro tabela que irá manter a auditoria.

ops$marcio@ORA10G> create table audit_tbl
  2  (    timestamp    date,
  3      who            varchar2(30),
  4      tname        varchar2(30),
  5      cname        varchar2(30),
  6      old            varchar2(2000),
  7      new            varchar2(2000)
  8  )
  9  /

Table created.

Package para identificar os valores. Notem o uso de morfologia na package.

ops$marcio@ORA10G>
ops$marcio@ORA10G> create or replace package audit_pkg
  2  as
  3      procedure check_val( l_tname in varchar2,
  4                               l_cname in varchar2,
  5                   l_new in varchar2,
  6                               l_old in varchar2 );
  7
  8      procedure check_val( l_tname in varchar2,
  9                               l_cname in varchar2,
 10                       l_new in date,
 11                               l_old in date );
 12
 13      procedure check_val( l_tname in varchar2,
 14                               l_cname in varchar2,
 15                   l_new in number,
 16                               l_old in number );
 17  end;
 18  /

Package created.

ops$marcio@ORA10G>
ops$marcio@ORA10G>
ops$marcio@ORA10G> create or replace package body audit_pkg
  2  as
  3
  4  procedure check_val( l_tname in varchar2,
  5                       l_cname in varchar2,
  6               l_new in varchar2,
  7                       l_old in varchar2 )
  8  is
  9  begin
 10      if ( l_new <> l_old or
 11           (l_new is null and l_old is not NULL) or
 12           (l_new is not null and l_old is NULL) )
 13      then
 14          insert into audit_tbl values
 15          ( sysdate, sys_context('USERENV','OS_USER'), upper(l_tname), upper(l_cname),
 16                               l_old, l_new );
 17      end if;
 18  end;
 19
 20  procedure check_val( l_tname in varchar2, l_cname in varchar2,
 21               l_new in date, l_old in date )
 22  is
 23  begin
 24      if ( l_new <> l_old or
 25           (l_new is null and l_old is not NULL) or
 26           (l_new is not null and l_old is NULL) )
 27      then
 28          insert into audit_tbl values
 29          ( sysdate, sys_context('USERENV','OS_USER'), upper(l_tname), upper(l_cname),
 30            to_char( l_old, 'dd-mon-yyyy hh24:mi:ss' ),
 31            to_char( l_new, 'dd-mon-yyyy hh24:mi:ss' ) );
 32      end if;
 33  end;
 34
 35  procedure check_val( l_tname in varchar2, l_cname in varchar2,
 36               l_new in number, l_old in number )
 37  is
 38  begin
 39      if ( l_new <> l_old or
 40           (l_new is null and l_old is not NULL) or
 41           (l_new is not null and l_old is NULL) )
 42      then
 43          insert into audit_tbl values
 44          ( sysdate, sys_context('USERENV','OS_USER'), upper(l_tname), upper(l_cname),
 45                                   l_old, l_new );
 46      end if;
 47  end;
 48
 49  end audit_pkg;
 50  /

Package body created.

Script para gerar a trigger genericamente, isso ajuda quando o esquema é grande, ou seja, tem muitas tabelas.

set serveroutput on
set feedback off
set embedded on
set heading off
spool tmp.sql

prompt create or replace trigger aud#&1
prompt after update on &1
prompt for each row
prompt begin

select '    audit_pkg.check_val( ''&1'', ''' || column_name ||
          ''', ' || ':new.' || column_name || ', :old.' ||
             column_name || ');'
from user_tab_columns where table_name = upper('&1')
/
prompt end;;
prompt /

spool off
set feedback on
set embedded off
set heading on

@tmp

Agora basta rodar o script contra as tabelas.

ops$marcio@ORA10G> @gera_trigger_generica emp
create or replace trigger aud#emp
after update on emp
for each row
begin
    audit_pkg.check_val( 'emp', 'EMPNO', :new.EMPNO, :old.EMPNO);
    audit_pkg.check_val( 'emp', 'ENAME', :new.ENAME, :old.ENAME);
    audit_pkg.check_val( 'emp', 'JOB', :new.JOB, :old.JOB);
    audit_pkg.check_val( 'emp', 'MGR', :new.MGR, :old.MGR);
    audit_pkg.check_val( 'emp', 'HIREDATE', :new.HIREDATE, :old.HIREDATE);
    audit_pkg.check_val( 'emp', 'SAL', :new.SAL, :old.SAL);
    audit_pkg.check_val( 'emp', 'COMM', :new.COMM, :old.COMM);
    audit_pkg.check_val( 'emp', 'DEPTNO', :new.DEPTNO, :old.DEPTNO);
end;
/

Trigger created.

Trigger criada, estamos prontos para o teste.
Abaixo, o espertinho vai aumentar o salário do KING de 10 para 50 mil e a tabela de auditoria vai mostrar mais abaixo.

ops$marcio@ORA10G> update emp set sal = 50000 where ename = 'KING';

1 row updated.

ops$marcio@ORA10G> select * from audit_tbl;

TIMESTAMP           WHO      TNAME CNAME OLD        NEW
------------------- -------- ----- ----- ---------- ----------
27/05/2005 02:14:52 marcio   EMP   SAL   10000      50000

1 row selected.