Como saber quais campos foram alterados na execução do Sql

Assuntos Gerais
#1

Galera bom dia…

Em primeiro peço desculpas por estar adicionando mais um tópico porém não consegui achar algum que eu pode-se fazer uma pergunta pertinente a esse assunto, porém moderadores se tiver e vcs poderem me encaminhar agradeço.

Então vamos lá.

Na empresa que trabalho estamos querendo implementar a parte de log de ações do usuário, a idéia é bem simples, saber quem,quando e o que alterou de determinada tabela.

Exemplo:

temos a tabela Cliente.

  table cliente(
     id      serial,
     nome  varchar(150),
     idade  numeric,
     rg       varchar (20),
    id_usuario   numeric
)

temos a tabela log;


 table log (
  id     serial,     
  data_hora timestamp defaul now(),
  nome_campo varchar(50),
  nome_tabela varchar(50),
  valor_antigo  varchar(100),
  valor_novo   varchar(100),
  id_usuario    numeric,
  usuario_banco varchar(100)
)

então criaria-mos uma trigger para monitorar a tabela cliente em todos os momentos, (Insert,Update,Delete) , a idéia seria criar uma função que verificasse se o dado foi alterado ou não, e se sofreu alteração grava os dados na tabela log.

Segue o exemplo de if dentro da função


 if (New.nome <> Old.nome) 
   insert into log ('nome',TG_RELNAME,old.nome,new.nome,old.id_usuario,current_user)

end if

Tudo bem esse If até que funcionária porém vamos imaginar em produção que as tabelas tem vários campos, e serão monitoradas varias tabelas, logo fica inviável fazer desta forma, o que eu gostaria de saber e se é possível em postgres criar um looping que percorreria os atributos do Record(New) e ai sim faria esse if

Exemplo:

 FOR i IN new.camposCount LOOP 

    if (New.campo<> Old.campo) 
        insert into log (campo,TG_RELNAME,old.campo,new.campo,old.id_usuario,current_user);

    end if;

END LOOP;

Dessa forma ficaria ótimo…

Então se alguém poder me ajudar eu agradeço e muito.

Desde já muito Obrigado.

#2

Não há necessidade de ficar verificando o campo que foi alterado.

Deixe que sua trigger (after update) insira os dados automaticamente como vc demonstrou no post.
Após isso vc pode inserir uma instrução de delete para todos os registros que valor_antigo == valor_novo.

É uma sugestão que pode funcionar!

Abraço!

Marco A.

#3

[quote=fanama]Não há necessidade de ficar verificando o campo que foi alterado.

Deixe que sua trigger (after update) insira os dados automaticamente como vc demonstrou no post.
Após isso vc pode inserir uma instrução de delete para todos os registros que valor_antigo == valor_novo.

É uma sugestão que pode funcionar!

Abraço!

Marco A.[/quote]

Obrigado pela atenção, o que vc disse realmente pode funcionar, porém caiu em contradição, pois vc menciona que não há necessidade de verificar qual o campo foi alterado, aí depois vc fala para criar uma trigger justamente para isso, então fica um re-trabalho.

E o que eu gostaria mesmo de saber e o seguinte, mesmo no seu exemplo a função que iria verificar depois, iria verificar campo a campo, então imagine uma tabela extensa, teria que fazer centenas de if’s para isso, porém eu queria automatizar isso com um laço.

#4

Dependendo do banco usado, isso já existe ou então é um “add-in”.

Por exemplo, para o Oracle: http://www.oracle-base.com/articles/10g/Auditing_10gR2.php

Procure por “audit log” ou “audit trail” + o nome do banco que você está usando.

#5

Talvez eu não tenha sido claro:

Não sugeri você fazer outro trigger, pelo contrário. No trigger que você mencionou estar fazendo você já está dando um insert na tabela de log, correto?

Continue fazendo assim, independente de saber se o campo foi alterado ou não. Pois o log será gravado após a alteração da tabela pois o trigger é after update certo? Grave todos os campos da tabela no log com os valores new e old.

Abaixo de seu insert vc adiciona uma linha para o delete (valor new==valor old, ou seja não houve alteração), assim vc assegura que na tabela de log ficarão somentes os registros que houveram alteração.

Falows!

#6

[quote=entanglement]

Procure por “audit log” ou “audit trail” + o nome do banco que você está usando. [/quote]

Muito obrigado pela resposta vou dar uma olhada sobre, porém só um pequeno detalhe o log é de uma aplicação Web, e o usuário que conecta na base e um para todos, o acesso no sistema é controlado na aplicação, logo eu perco a rastreabilidade se for feito isso somente pelo banco, por isso que eu preciso de algo que pegue os dados que estão sendo inseridos, e não só as ações.

#7

Ah, entendi. Uma forma nojenta de implementar isso (que é feita em várias instituições financeiras, aliás) é negar todo e qualquer acesso do tipo SELECT, INSERT, UPDATE ou DELETE a esse usuário de sistema e só permitir-lhe acesso via stored procedures. Cada stored procedure tem código adequado para efetuar o select, insert, delete, update ou alguma transação mais complexa, e também faz a auditoria.

#8

[quote=entanglement]Ah, entendi. Uma forma nojenta de implementar isso.
[/quote]

Kkkk, obrigado pela sugestão, mais para aumentar o leque de ajuda, aqui nós utilizamos o hibernate para fazer os CRUDS, então se alguém souber de alguma forma também de implementar isso com o hibernate e bem vindo.

#9

Pelo o que eu entendi, você quer criar uma trigger em cada tabela auditada que gravará esse log certo?

Como cada trigger é presa em uma tabela específica, você saberá na hora exatamente quais são os campos, não?

Essa forma dinâmica é apenas para poupar tempo com a digitação/manutenção do código de auditoria?

Fazendo analogia ao java, seria como usar reflection dentro de um bean para descobrir as propriedades do próprio bean…
Você sabe quais são os campos, mas se tiver muitos é trabalhoso digitar um a um…

Pensando nisso, ao invés de deixar a trigger dinâmica, você poderia deixar a criação da trigger dinâmica…

Você poderia criar um gerador de triggers de auditoria, que leria a tabela de objetos do sistema para criar uma trigger de auditoria…
Com isso você não teria o trabalho de digitar todos campos, e sua trigger teria uma performance melhor…

Apenas uma dica: lembre-se que tanto INSERT, UPDATE, DELETE podem afetar mais de uma linha por vez.
Uma falha comum em triggers é considerar que apenas um registro está sendo manipulado.

#10

O seu entendimento a minha pergunta foi perfeita, a idéia seria realmente algo parecido com reflection do java, e no postgre realmente a trigger e para cada tabela porém a função que a trigger executa não, então você pode usar 10 trigger’s utilizando a mesma função, por isso a minha idéia de deixar dinâmico, ai eu teria 10 trigger’s representando tabelas diferentes e a com mesma função fazendo o trabalho.

Mas vou dar uma estudada desse ponto de vista de deixar a criação da trigger e da função dinamicamente.

#11

Galera obrigado a todos que se pronunciaram e tentaram de alguma forma me ajudar.
Mas com um pouco mais de pesquisa consegui fazer a seguinte função.

CREATE OR REPLACE FUNCTION log ()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
    ri RECORD;
    campo_novo TEXT;
    campo_antigo text;
    
BEGIN

    if ( TG_OP = 'INSERT') then	


	insert into log    (id_campo  ,nome_campo,nome_tabela                  , valor_antigo ,valor_novo,operacao,usuario_banco) values 
				( new.id      ,'novo'           ,quote_ident(TG_relname), 'novo'          ,'novo'       ,tg_op      ,user);
    else
	 if ( TG_OP = 'DELETE') then
				
	        insert into log  (id_campo       ,nome_campo ,nome_tabela                  , valor_antigo ,valor_novo,operacao,usuario_banco) values 
				     (old.id  ,'apagado'      ,quote_ident(TG_relname), 'apagado'     ,'apagado'  ,tg_op      ,user);
	 end if;
    end if;			



    FOR ri IN
        SELECT  column_name
        FROM information_schema.columns
        WHERE
            table_schema = quote_ident(TG_TABLE_SCHEMA)
        AND table_name = quote_ident(TG_TABLE_NAME)
        ORDER BY ordinal_position
    LOOP
	
	
	if ( TG_OP = 'UPDATE') then		
	
		Execute 'SELECT ($1).' || ri.column_name || '::text' into campo_antigo using old;
		Execute 'SELECT ($1).' || ri.column_name || '::text' into campo_novo using NEW;

		if campo_novo <> campo_antigo then
			insert into log    (id_campo ,nome_campo     ,nome_tabela                  , valor_antigo   ,valor_novo   ,operacao,usuario_banco) values 
						(old.id       ,ri.column_name ,quote_ident(TG_relname), campo_antigo ,campo_novo ,tg_op     ,user);
                end if;
        end if;       
        

    END LOOP;
    RETURN NEW;
END;
$$;

E criamos a trigger que chama essa função

CREATE TRIGGER  "trg_tabela_log" BEFORE INSERT or UPDATE or DELETE 
ON tabela FOR EACH ROW 
    EXECUTE PROCEDURE  log();

É claro quem fizer melhorias post aí para agente também poder melhorar os nossos códigos…

Muito Obrigado a todos.

#12