Criando colunas de auditoria em tabelas Oracle

Olá pessoal,

é comum a inclusão de colunas de auditoria quando se cria um sistema.

As colunas de auditoria são as que armazenam informações sobre a criação e alteração do registro que se encontra na tabela. Os nomes de coluna mais comuns em Português são: CRIADO_POR, CRIADO_EM, ALTERADO_POR e ALTERADO_EM.

Primeiramente temos que criar essas 4 colunas em todas as tabelas (Caso já não existam).

Calma! Você não precisará entrar em todas as suas dezenas (ou centenas) de tabelas e alterar.

Segue um script para fazer esta atividade de forma automática.

Copie e cole o script abaixo no Comandos SQL e pressione "Executar" (ou "Run")

--------------------INICIO SCRIPT---------------------
----------------Inclui colunas de auditoria-------------
BEGIN

FOR cur IN (select * from user_tables) LOOP

   EXECUTE IMMEDIATE 'alter table ' || cur.table_name || ' add (CRIADO_POR VARCHAR2(255))';
   EXECUTE IMMEDIATE 'alter table ' || cur.table_name || ' add (CRIADO_EM DATE)';
   EXECUTE IMMEDIATE 'alter table ' || cur.table_name || ' add (ALTERADO_POR VARCHAR2(255))';
   EXECUTE IMMEDIATE 'alter table ' || cur.table_name || ' add (ALTERADO_EM DATE)';

END LOOP;

END;
------------------------FIM SCRIPT-------------------------

Ao final da execução, aparecerá uma mensagem de sucesso, conforme imagem abaixo.

Para ter certeza de que deu certo, vá ao Browser de Objetos e confira se as colunas foram criadas.

A melhor maneira de garantir que suas colunas de auditoria serão preenchidas corretamente, é por meio de trigger no banco de dados.

Para facilitar o trabalho, segue um script que cria as triggers para as tabelas com tamanho menor que 26 (As tabelas com tamanho maior que 26 deverão ser criadas manualmente).

O script deve ser executado no Comandos SQL também.

Observação: O trecho que utiliza APEX$SESSION funciona apenas a partir do Apex 5, portanto deve ser alterado para versões anteriores do Apex.

--------------------INICIO SCRIPT---------------------
DECLARE
   v_comando VARCHAR2(4000);

BEGIN

FOR cur IN (select * from user_tables where length(table_name) <=26) LOOP 
   v_comando := 'create or replace trigger BIU_'||cur.table_name||' before insert or update on "'||cur.table_name||'" 
for each row 
begin
    if inserting then
        :new.criado_em := sysdate;
        :new.criado_por := nvl(sys_context(''APEX$SESSION'',''APP_USER''),user);
    end if;
    :new.alterado_em := sysdate;
    :new.alterado_por := nvl(sys_context(''APEX$SESSION'',''APP_USER''),user);
end;';
htp.p(v_comando);
EXECUTE IMMEDIATE v_comando;

END LOOP;

END;
------------------------FIM SCRIPT-------------------------

Verifique se o comando foi executado corretamente. Você pode ir também ao Browser de Objetos e conferir se as triggers foram criadas sem problemas.

Para verificar quais tabelas devem ter as triggers criadas  manualmente, execute o SELECT abaixo no Comandos SQL.

select * from user_tables where length(table_name) >26

Utilize o CREATE TRIGGER abaixo no Comandos SQL substituindo as tags <<nome_tabela_abreviada>> e <<nome_tabela>> pelo nome desejado.

create or replace trigger BIU_<<nome_tabela_abreviado>> before insert or update on <<nome_tabela>> 
for each row 
begin
    if inserting then
        :new.criado_em := sysdate;
        :new.criado_por := nvl(sys_context('APEX$SESSION','APP_USER'),user);
    end if;
    :new.alterado_em := sysdate;
    :new.alterado_por := nvl(sys_context('APEX$SESSION','APP_USER'),user);
end; 

Pronto! Agora basta inserir algum registro por algum formulário ou fazer algum comando DML diretamente no banco que as colunas serão preenchidas.

Espero que tenha gostado!

Até a próxima.