SQL

Criando um log utilizando gatilhos no SQL

05/06/2022SQL

Neste artigo vamos ver como fazer um log das queries que foram utilizadas para fazer INSERT ou UPDATE em alguma tabela do SQL. Vamos ver exemplos no MySQL e no SQL Server.

 

Para executar alguma ação ao inserir ou atualizar registros numa determinada tabela no SQL utilizamos algo chamado Trigger. As triggers são gatilhos que são executadas de forma automática em determinadas situações, como por exemplo inserção ou atualização de dados. Vamos ver neste artigo como criar estes gatilhos no MySQL e no SQL Server.

 

Começando pelo MySQL, vamos primeiro criar uma tabela de testes:

 

CREATE TABLE tabela_teste

(

  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

  nome  VARCHAR(50) NOT NULL,

  email VARCHAR(80) NOT NULL,

  data datetime DEFAULT CURRENT_TIMESTAMP

)

 

E na sequência vamos criar a tabela que vai armazenar nosso log:

 

CREATE TABLE tabela_log

(

  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

  consulta VARCHAR(1024) NOT NULL,

  data datetime DEFAULT CURRENT_TIMESTAMP

);

 

Para fazer um log dos inserts e updates temos que criar uma trigger (gatilho).  O código a seguir vai criar um gatilho para ser executado ao inserir ou atualizar informações em nosso banco:

 

DELIMITER |

 

CREATE TRIGGER log_queries BEFORE INSERT ON `tabela_teste`

FOR EACH ROW

BEGIN

    DECLARE consulta_atual VARCHAR(1024);

    SET consulta_atual = (SELECT info FROM INFORMATION_SCHEMA.PROCESSLIST WHERE id = CONNECTION_ID());

    INSERT INTO `tabela_log`(`consulta`) VALUES (consulta_atual);

END;

|

DELIMITER ;

 

Ao criar a nossa trigger(gatilho) devemos informar um nome para o gatilho, quando ele deve ser executado e qual a tabela que ele deve observar.

No MySQL podemos ter acesso a consulta que disparou o nosso gatillho utilizando o information schema

 

SELECT info FROM INFORMATION_SCHEMA.PROCESSLIST WHERE id = CONNECTION_ID()

 

Para validar se o gatilho está funcionando corretamente, vamos adicionar e atualizar registros em nossa tabela de testes

 

INSERT INTO tabela_teste (nome,email) VALUES ('Daniel', 'teste1@teste.com.br');

INSERT INTO tabela_teste (nome,email) VALUES ('Lucas', 'teste2@teste.com.br');

INSERT INTO tabela_teste (nome,email) VALUES ('Matheus', 'teste3@teste.com.br');

UPDATE tabela_teste SET email = 'teste4@teste.com.br' WHERE id = 3;

 

Agora vamos conferir se as consultas foram gravadas na tabela de log

 

SELECT * from tabela_log

 

Você vai perceber que apenas os INSERTs foram gravados. Perceba que ao criar o nosso gatilho, depois de informar o nome do gatilho, que é "log_queries", informados BEFORE INSERT.Isso quer dizer que ele apenas vai observar as operações de INSERT, por isso o nosso updade não gravou. Para registrar o nosso UPDATE, será necessário criar um segundo gatilho. Vai haver duas alterações, vamos inserir um novo nome de gatilho e substituir a ação BEFORE INSERT por BEFORE UPDATE

 

DELIMITER |

 

CREATE TRIGGER log_queries_update BEFORE UPDATE ON `tabela_teste`

FOR EACH ROW

BEGIN

    DECLARE consulta_atual VARCHAR(1024);

    SET consulta_atual = (SELECT info FROM INFORMATION_SCHEMA.PROCESSLIST WHERE id = CONNECTION_ID());

    INSERT INTO `tabela_log`(`consulta`) VALUES (consulta_atual);

END;

|

DELIMITER ;

 

Execute novamente o update

 

UPDATE tabela_teste SET email = 'teste4@teste.com.br' WHERE id = 3;

 

e confira novamente a tabela de log:

 

SELECT * from tabela_log

 

Agora temos os insert e updates registrados em nossa tabela de log!

 

Chegou a hora de ver como podemos fazer o mesmo no SQL Server

 

Vamos utilizar como referência as mesmas tabelas criadas no exemplo do MySQL. Os inserts e updates serão os mesmos. Veja a seguir o código para criar as tabelas no SQL Server:

 

CREATE TABLE tabela_teste (

    id INT PRIMARY KEY IDENTITY (1, 1),

    nome VARCHAR (50) NOT NULL,

    email VARCHAR (80) NOT NULL,

    data DATETIME default CURRENT_TIMESTAMP

);

 

CREATE TABLE tabela_log (

    id INT PRIMARY KEY IDENTITY (1, 1),

    consulta VARCHAR (1024) NOT NULL,

    data DATETIME default CURRENT_TIMESTAMP

);

 

No SQL Server o gatilho ficaria da seguinte forma:

 

CREATE TRIGGER log_queries

ON tabela_teste

    FOR INSERT, UPDATE

AS

    DECLARE @consulta varchar(1024)

DECLARE @TabelaConsulta TABLE (

        EventType NVARCHAR(30), 

        [Parameters] INT, 

        EventInfo NVARCHAR(MAX)

    ) 

 

   INSERT INTO @TabelaConsulta 

   EXEC('DBCC INPUTBUFFER(@@SPID)');

   

   SET @consulta = (SELECT TOP(1) EventInfo FROM @TabelaConsulta)

   INSERT INTO tabela_log (consulta)

     VALUES

     (

     @consulta

     );

 

 

No SQL Server podemos ver a consulta que está sendo executado num determinado processo utilizando o seguinte comando

 

DBCC INPUTBUFFER(@@SPID)

 

A função recebe um parâmetro que é o id do processo (spid). Para ver quais os processos que estão sendo executados, podemos rodar o comando 

 

EXEC sp_who

 

O spid aparece na primeira coluna de resultados.

 

Quando você executa o comando DBCC INPUTBUFFER ele retorna uma linha contendo o EventType, os parâmetros e no final o EventInfo. Nós precisamos do EventInfo, já que nele vai estar a consulta que disparou o gatilho.

 

O ID do processo pode ser capturado dentro do gatilho. Como o retorno do DBCC INPUTBUFFER é uma tabela, precisamos criar uma tabela interna para armazenar o seu conteudo e depois fazer uma consulta para extrair o EventInfo. Com ele em mãos, podemos gravar em nossa tabela de log.

 

Perceba que ao contrário do MySQL, onde o INSERT e o UPDATE tiveram que ser registrados em gatilhos separados, no SQL Server é possível capturar os dois eventos numa única ação.

 

Um outro detalhe importante neste gatilho do SQL Server é que se você executar todos os INSERTs e UPDATES ao mesmo tempo ele vai gerar uma linha de log única para todas as operações.  Então para fins de testes você deverá executar de forma individual cada INSERT ou UPDATE.

Outros conteudos que podem ser de seu interesse

Trabalhando com datas no SQL
28/03/2021SQL

Trabalhando com datas no SQL

Conheça as funções que permitem trabalhar com datas no SQL

Saiba mais...
SQL vs Mongo - Operações básicas
08/12/2019SQL

SQL vs Mongo - Operações básicas

Um comparativo entre as operações básicas no SQL e seus equivalentes no Mongo

Saiba mais...

Conteúdo sobre banco de dados sem complicação!

MySQL - Referência Rápida

 

SQL Server - Referência Rápida

 

SQL vs Mongo