Criar Trigger de Log de Auditoria

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
Responder
lucasraynerr
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Mensagens: 8
Registrado em: Seg, 15 Set 2014 9:37 pm

Boa noite,

estou criando uma trigger para salvar os logs de registros em uma tabela de auditoria e gostaria de saber se consigo setar na tabela de auditoria qual foi a coluna alterada/atualizada e de qual tabela é essa coluna. Preciso apresentar para o auditor essas informações.

Abaixo, a estrutura da tabela de auditoria:

Selecionar tudo

CREATE TABLE AUDIT
(
 ID_AUDIT NUMBER NOT NULL,
 USUARIO VARCHAR2(45) NOT NULL
 DATA_REGISTRO DATE NOT NULL,
 MAQUINA_USU VARCHAR2(45) NOT NULL,
 TIPO_ACAO VARCHAR2(45) NOT NULL,
 NM_TABELA VARCHAR2(45) NOT NULL,
 NM_COLUNA VARCHAR2(45) NOT NULL,
 NEW_ID_VALOR NUMBER,
 OLD_ID_VALOR NUMBER,
 NEW_VALOR VARCHAR2(45),
 OLD_VALOR VARCHAR2(45)
);
Avatar do usuário
tiago_pimenta
Rank: DBA Júnior
Rank: DBA Júnior
Mensagens: 213
Registrado em: Qua, 29 Jun 2011 9:49 am
Localização: Barretos / SP

Lucas,

No meu trabalho, cada tabela tem uma outra tabela de auditoria, assim a parte de "pegar" qual tabela está "sofrendo" a alteração não seria necessário, seu caso precisa realmente de 1 tabela só para todas as modificações do RDBMS ?
lucasraynerr
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Mensagens: 8
Registrado em: Seg, 15 Set 2014 9:37 pm

Sim. Foi especificado que no projeto deveria ter apenas uma tabela de auditoria justamente para evitar a criação de várias tabelas, facilitando assim futuras manutenções.
Avatar do usuário
tiago_pimenta
Rank: DBA Júnior
Rank: DBA Júnior
Mensagens: 213
Registrado em: Qua, 29 Jun 2011 9:49 am
Localização: Barretos / SP

É... então complicou, acabei de ver as triggers de auditoria aqui do serviço e elas não pegam a coluna da modificação, só o valor.
lucasraynerr
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Mensagens: 8
Registrado em: Seg, 15 Set 2014 9:37 pm

Tiago, eu estava fazendo as tabelas de auditoria como você falou, criando uma tabela de auditoria para cada tabela do banco. Porém me solicitaram essa mudança.

Eu até consigo registrar o nome da tabela está, porém, a minha grande dificuldade tem sido em registrar o nome da coluna.
Avatar do usuário
tiago_pimenta
Rank: DBA Júnior
Rank: DBA Júnior
Mensagens: 213
Registrado em: Qua, 29 Jun 2011 9:49 am
Localização: Barretos / SP

Como você consegue "pegar" o nome da tabela ? Qual o comando ?
lucasraynerr
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Mensagens: 8
Registrado em: Seg, 15 Set 2014 9:37 pm

Na verdade, eu apenas crio uma variável e faço ela receber o nome da tabela a qual a Trigger está referenciando, é meio que um "jeitinho brasileiro".

Ex:

Selecionar tudo

DECLARE
       NM_TABELA VARCHAR2(65);

      BEGIN
       NM_TABELA := 'TABELA_AUDITADA';
A única diferença do procedimento que você utiliza na sua empresa, é que existe apenas uma tabela de auditoria para todas as tabelas do banco mas ainda sim existe uma trigger de auditoria para cada tabela.
souldeath
Rank: Programador Sênior
Rank: Programador Sênior
Mensagens: 48
Registrado em: Qua, 25 Ago 2010 9:39 am
Localização: Limeira

Lucas,

Acho que o unico jeito é comparar e inserir de forma fixa em uma variavel mesmo:

Selecionar tudo

if :old.coluna1 != :new.coluna 1 then
  nm_coluna = 'nome coluna';
  [...]
end if;
Não tem nenhuma função que retorne o nome da coluna.

Você também pode especificar uma condição para a trigger, para que só seja disparada na condição desejada:
https://asktom.oracle.com/pls/asktom/f? ... 0177144100
ballboas
Rank: Analista Júnior
Rank: Analista Júnior
Mensagens: 98
Registrado em: Qui, 02 Ago 2007 3:06 pm
Localização: sp
Érico Balboa

A pergunta mais cretina é aquela que não é feita

Acredito que a única forma dinâmica de você capturar isso seja através de uma consulta em tempo de execução na all_tab_columns, porém, você precisaria verificar coluna a coluna se os valores :new e :old são diferentes, pelo caso de uso que está sendo sugerido, que é usar apenas uma tabela de log, precisa tomar muito cuidado com performance, já que é um processo um tanto quanto custoso para uma trigger, principalmente se você trabalha com uma grande massa de dados.

Outro ponto que precisa antentar é se todas as tabelas do seu sistema possuem chave primária simples, ou seja, com apenas uma coluna, pelo que percebi você usa as colunas NEW_ID_VALOR e OLD_ID_VALOR para guardar isso, você só precisaria guardar o new e o old caso seja permitido alterar a Pk da tabela, não aconselho.
Responder
  • Informação