é 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-------------------------
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.
