Usando triggers para criar logs no MySQL

Há um tempo atrás onde trabalho, surgiu a necessidade de saber quais informações eram inseridas eou alteradas e quando estas alterações ocorriam. O sistema que realizava estas alterações tinha um código extremamente mal escrito, então quanto menos alterações fossem feitas no código, melhor. A solução a que cheguei foi utilizar triggers nas tabelas que necessitavam destes logs.

O tutorial utiliza o MySQL, porém a sintaxe para outros SGBD's é bem similar (se não for igual)

Primeiro temos a tabela que desejamos criar os logs:

create table venda (  
      id int auto_increment primary key,  
      dia datetime,  
      valor float  
);

Até aqui sem novidades, este é apenas o código para criar a hipotética tabela de venda, é nesta tabela onde estão as informações q desejaremos “logar”.

O próximo passo é criar a tabela que armazenará as informações:

create table venda_log (  
      log_id int auto_increment primary key,  
      old_id int,  
      old_dia datetime,  
      old_valor float,  
      new_id int,  
      new_dia datetime,  
      new_valor int,  
      acao varchar(6),  
      usuario varchar(50),  
      hora timestamp  
 );  

Nesta tabela temos um campo de id para chave primaria do log, dois campos para cada campo da tabela Venda, um com o prefixo old e outro com o prefixo new. Temos o campo acao para sabermos o que foi feito na tabela, o campo usuario para sabermos qual usuario (do mysql) fez a alteração e o campo hora para sabermos quando esta alteração foi feita.

Agora temos que criar as triggers que alimentarão esta tabela:

create trigger venda_log_insert after insert on venda  
 for each row  
           insert into venda_log(old_id, old_dia, old_valor, new_id, new_dia, new_valor, acao, usuario, hora)   
                     values(null,null,null, new.id, new.dia, new.valor, 'INSERT', user(), now());  

Se você nunca criou uma trigger pode estranhar o código acima, mas é mais simples do que parece:

• create trigger = código para criar a trigger.
• venda_log_insert = nome da triger.
• after = tempo de execução da trigger. Before para executar a trigger antes do comando que causou seu disparo e after para executar a trigger depois do comando que causou seu disparo.
• insert = o evento que dispara a trigger, poderia ser por exemplo update ou delete
• venda = a tabela a que a trigger está associada. Esta trigger por exemplo, será disparada quando acontecer um insert na tabela venda.
• for each row = indica que o corpo da trigger começou, o que vem depois disso é o que de fato a trigger faz.

Outro detalhe importante sobre a trigger é a utilização de “new”, dentro da trigger temos variaveis que representam os campos (da tabela indicada depois do on) antes e depois da alteração. Para utilizarmos o campo antes, usamos old.nome_do_campo e para o campo depois da alteração usamos new.nome_do_campo.

Em triggers de insert, não temos os campos old pois antes da execução do comando a linha ainda não existe. Em triggers de delete não temos os campos new pois não existe linha depois da execução do comando e em triggers do tipo update temos os dois tipos de campo.

O que esta trigger faz então é simples, pega os valores que foram inseridos na tabela indicada e loga eles em uma tabela, informando também o usuario e a hora do acontecimento. Para deixarmos o log completo vamos criar uma trigger para delete e uma para update:

 create trigger venda_log_update after update on venda  
 for each row  
      insert into venda_log(old_id, old_dia, old_valor, new_id, new_dia, new_valor, acao, usuario, hora)   
                values(old.id, old.dia ,old.valor, new.id, new.dia, new.valor, 'UPDATE', user(), now());  
 create trigger venda_log_delete after delete on venda  
 for each row  
      insert into venda_log(old_id, old_dia, old_valor, new_id, new_dia, new_valor, acao, usuario, hora)   
                values(old.id, old.dia ,old.valor, null, null, null, 'DELETE', user(), now());  

Os logs estão prontos, para testar vamos realizar algumas operações na nossa tabela:

 insert into venda (dia, valor)values ('2014/03/09 19:43:00',137.00);  
 update venda set valor = 777 where id = 1;  
 delete from venda where id = 1;  

Ao executarmos um select na nossa tabela de log temos 3 linhas:

A primeira linha nos mostra que as 19:45 foi feito um insert, os campos com prefixo new dizem quais valores foram inseridos. A segunda linha nos diz que às 19:49 os valores de uma venda foram alteradas, o valor antigo da venda era 137 e o novo valor passou a ser 777. A ultima linha mostra que havia uma venda com os valores mostrados no old, mas ela foi deletada.

Esta é apenas uma das maneiras de se criar um log em seu banco de dados, se tiver sugestões, críticas, opiniões ou quaisquer pensamentos aleatórios, comente ^^