Criando um log utilizando gatilhos no SQL
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.
