SQL Server - Como identificar a query que disparou uma trigger de DML numa tabela

Post Views 1,495 views

Neste artigo

Reading time 5 minutes

Como identificar a query que disparou uma trigger de DML numa tabela? Li essa dúvida num grupo do Telegram e resolvi escrever um artigo sobre isso.

Introduction

Como vocês sabem, existem várias soluções para se implementar um log de auditoria com as alterações de dados em uma determinada tabela:

Uma das soluções mais utilizadas para isso, é a utilização de triggers de DML (Saiba como implementar isso clicando aqui)

Em alguns cenários, é importante identificar qual a consulta que acabou disparando a trigger para talvez identificar a origem dos dados que estão sendo inseridos a partir do SQL. E é isso que aprenderemos como fazer nesse post.

Caso você queira mais detalhes da origem desse comando e identificar toda a cadeia de execução da trigger, sugiro a leitura do artigo SQL Server – Como identificar e monitorar a execução de triggers.

Creating the test base

Para a demonstração dos códigos desse artigo, vou compartilhar com vocês o script que utilizei para montar essa tabela e a trigger utilizada para auditar as alterações. Vou utilizar o mesmo exemplo do artigo SQL Server – Como criar um histórico de alterações de dados para suas tabelas (logs para auditoria).

Criação das tabelas
Clique aqui para visualizar código da criação das tabelas

IF (OBJECT_ID('dbo.Cliente') IS NOT NULL) DROP TABLE dbo.Cliente
CREATE TABLE dbo.Cliente (
    Id_Cliente INT IDENTITY(1, 1),
    Nome VARCHAR(100),
    Data_Nascimento DATETIME,
    Salario FLOAT
)

INSERT INTO dbo.Cliente
VALUES 
    ('João', '1981-05-14', 4521),
    ('Marcos', '1975-01-07', 1478.58),
    ('André', '1962-11-11', 7151.45),
    ('Simão', '1991-12-18', 2584.97),
    ('Pedro', '1986-11-20', 987.52),
    ('Paulo', '1974-08-04', 6259.14),
    ('José', '1979-09-01', 5272.13)


-- Criando a tabela com a mesma estrutura da original, mas adicionando colunas de controle
IF (OBJECT_ID('dbo.Cliente_Log') IS NOT NULL) DROP TABLE dbo.Cliente_Log
CREATE TABLE dbo.Cliente_Log (
    Id INT IDENTITY(1, 1),
    Dt_Atualizacao DATETIME DEFAULT GETDATE(),
    [Login] VARCHAR(100),
    Hostname VARCHAR(100),
    Operacao VARCHAR(20),

    -- Dados da tabela original
    Id_Cliente INT,
    Nome VARCHAR(100),
    Data_Nascimento DATETIME,
    Salario FLOAT
)
GO

IF (OBJECT_ID('dbo.Cliente') IS NOT NULL) DROP TABLE dbo.Cliente

CREATE TABLE dbo.Cliente (

Id_Cliente INT IDENTITY(1, 1),

Name VARCHAR(100),

Data_Nascimento DATETIME,

Salario FLOAT

)

INSERT INTO dbo.Cliente

VALUES

('João', '1981-05-14', 4521),

('Marcos', '1975-01-07', 1478.58),

('André', '1962-11-11', 7151.45),

('Simão', '1991-12-18', 2584.97),

('Pedro', '1986-11-20', 987.52),

('Paulo', '1974-08-04', 6259.14),

('José', '1979-09-01', 5272.13)

-- Criando a tabela com a mesma estrutura da original, mas adicionando colunas de controle

IF (OBJECT_ID('dbo.Cliente_Log') IS NOT NULL) DROP TABLE dbo.Cliente_Log

CREATE TABLE dbo.Cliente_Log (

Id INT IDENTITY(1, 1),

Dt_Atualizacao DATETIME DEFAULT GETDATE(),

[Login] VARCHAR(100),

Hostname VARCHAR(100),

Operacao VARCHAR(20),

-- Dados da tabela original

Id_Cliente INT,

Name VARCHAR(100),

Data_Nascimento DATETIME,

Salario FLOAT

)

Criação da trigger:
Clique aqui para visualizar o código da trigger

IF ((SELECT COUNT(*) FROM sys.triggers WHERE name = 'trgHistorico_Cliente' AND parent_id = OBJECT_ID('dbo.Cliente')) &gt; 0) DROP TRIGGER trgHistorico_Cliente
GO

CREATE TRIGGER trgHistorico_Cliente ON dbo.Cliente -- Tabela que a trigger será associada
AFTER INSERT, UPDATE, DELETE AS
BEGIN
    
    SET NOCOUNT ON

    DECLARE 
        @Login VARCHAR(100) = SYSTEM_USER, 
        @HostName VARCHAR(100) = HOST_NAME(),
        @Data DATETIME = GETDATE()
        

    IF (EXISTS(SELECT * FROM Inserted) AND EXISTS (SELECT * FROM Deleted))
    BEGIN
        
        INSERT INTO dbo.Cliente_Log
        SELECT @Data, @Login, @HostName, 'UPDATE', *
        FROM Inserted

    END
    ELSE BEGIN

        IF (EXISTS(SELECT * FROM Inserted))
        BEGIN

            INSERT INTO dbo.Cliente_Log
            SELECT @Data, @Login, @HostName, 'INSERT', *
            FROM Inserted

        END
        ELSE BEGIN

            INSERT INTO dbo.Cliente_Log
            SELECT @Data, @Login, @HostName, 'DELETE', *
            FROM Deleted

        END

    END

END
GO

IF ((SELECT COUNT(*) FROM sys.triggers WHERE name = 'trgHistorico_Cliente' AND parent_id = OBJECT_ID('dbo.Cliente')) > 0) DROP TRIGGER trgHistorico_Cliente

CREATE TRIGGER trgHistorico_Cliente ON dbo.Cliente -- Tabela que a trigger será associada

AFTER INSERT, UPDATE, DELETE AS

BEGIN

SET NOCOUNT ON

DECLARE

@Login VARCHAR(100) = SYSTEM_USER,

@HostName VARCHAR(100) = HOST_NAME(),

@Data DATETIME = GETDATE()

IF (EXISTS(SELECT * FROM Inserted) AND EXISTS (SELECT * FROM Deleted))

BEGIN

INSERT INTO dbo.Cliente_Log

SELECT @Data, @Login, @HostName, 'UPDATE', *

FROM Inserted

END

ELSE BEGIN

IF (EXISTS(SELECT * FROM Inserted))

BEGIN

INSERT INTO dbo.Cliente_Log

SELECT @Data, @Login, @HostName, 'INSERT', *

FROM Inserted

END

ELSE BEGIN

INSERT INTO dbo.Cliente_Log

SELECT @Data, @Login, @HostName, 'DELETE', *

FROM Deleted

END

Faço algumas alterações na tabela original:

INSERT INTO dbo.Cliente
VALUES ('Bartolomeu', '1975-05-28', 6158.74)

UPDATE dbo.Cliente
SET Salario = Salario * 1.5
WHERE Nome = 'Bartolomeu'

DELETE FROM dbo.Cliente
WHERE Nome = 'André'

UPDATE dbo.Cliente
SET Salario = Salario * 1.1
WHERE Id_Cliente = 2

UPDATE dbo.Cliente
SET Salario = 10, Nome = 'Judas Iscariodes', Data_Nascimento = '06/06/2066'
WHERE Id_Cliente = 1

INSERT INTO dbo.Cliente

VALUES ('Bartolomeu', '1975-05-28', 6158.74)

UPDATE dbo.Cliente

SET Salario = Salario * 1.5

WHERE Name = 'Bartolomeu'

DELETE FROM dbo.Cliente

WHERE Name = 'André'

UPDATE dbo.Cliente

SET Salario = Salario * 1.1

WHERE Id_Cliente = 2

UPDATE dbo.Cliente

SET Salario = 10, Name = 'Judas Iscariodes', Data_Nascimento = '06/06/2066'

WHERE Id_Cliente = 1

E podemos ver que a auditoria está funcionando conforme o esperado:

Como identificar a query que disparou a trigger

Agora descobriremos como identificar a query que disparou a trigger.

Para isso, vamos alterar a trigger e incluir o comando INPUTBUFFER para capturar a query e armazenar essa informação na tabela de log.

A sintaxe de utilização do comando é assim:

DECLARE @SqlQuery VARCHAR(MAX)

DECLARE @TableSqlQuery TABLE (
    EventType NVARCHAR(30), 
    [Parameters] INT, 
    EventInfo NVARCHAR(MAX)
) 

INSERT INTO @TableSqlQuery 
EXEC('DBCC INPUTBUFFER(@@SPID)') 

SET @SqlQuery = (SELECT TOP(1) EventInfo FROM @TableSqlQuery)

DECLARE @SqlQuery VARCHAR(MAX)

DECLARE @TableSqlQuery TABLE (

EventType NVARCHAR(30),

[Parameters] INT,

EventInfo NVARCHAR(MAX)

)

INSERT INTO @TableSqlQuery

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

SET @SqlQuery = (SELECT TOP(1) EventInfo FROM @TableSqlQuery)

Adicionaremos o campo que armazenará a query na tabela:

ALTER TABLE dbo.Cliente_Log ADD Ds_Query VARCHAR(MAX)

1	ALTER TABLE dbo.Cliente_Log ADD Ds_Query VARCHAR(MAX)

E agora alteramos a trigger:
Clique aqui para visualizar código da trigger

IF ((SELECT COUNT(*) FROM sys.triggers WHERE name = 'trgHistorico_Cliente' AND parent_id = OBJECT_ID('dbo.Cliente')) > 0) DROP TRIGGER trgHistorico_Cliente
GO
 
CREATE TRIGGER trgHistorico_Cliente ON dbo.Cliente -- Tabela que a trigger será associada
AFTER INSERT, UPDATE, DELETE AS
BEGIN
    
    SET NOCOUNT ON
 
    DECLARE 
        @Login VARCHAR(100) = SYSTEM_USER, 
        @HostName VARCHAR(100) = HOST_NAME(),
        @Data DATETIME = GETDATE()
        

    ------- INÍCIO DA ALTERAÇÃO -------

    DECLARE @SqlQuery VARCHAR(MAX)

    DECLARE @TableSqlQuery TABLE (
        EventType NVARCHAR(30), 
        [Parameters] INT, 
        EventInfo NVARCHAR(MAX)
    ) 

   INSERT INTO @TableSqlQuery 
   EXEC('DBCC INPUTBUFFER(@@SPID)') 
   
   SET @SqlQuery = (SELECT TOP(1) EventInfo FROM @TableSqlQuery)

   ------- FIM DA ALTERAÇÃO -------

 
    IF (EXISTS(SELECT * FROM Inserted) AND EXISTS (SELECT * FROM Deleted))
    BEGIN
        
        INSERT INTO dbo.Cliente_Log
        SELECT @Data, @Login, @HostName, 'UPDATE', *, @SqlQuery -- ADICIONEI O @SqlQuery aqui
        FROM Inserted
 
    END
    ELSE BEGIN
 
        IF (EXISTS(SELECT * FROM Inserted))
        BEGIN
 
            INSERT INTO dbo.Cliente_Log
            SELECT @Data, @Login, @HostName, 'INSERT', *, @SqlQuery -- ADICIONEI O @SqlQuery aqui
            FROM Inserted
 
        END
        ELSE BEGIN
 
            INSERT INTO dbo.Cliente_Log
            SELECT @Data, @Login, @HostName, 'DELETE', *, @SqlQuery -- ADICIONEI O @SqlQuery aqui
            FROM Deleted
 
        END
 
    END
 
END
GO

IF ((SELECT COUNT(*) FROM sys.triggers WHERE name = 'trgHistorico_Cliente' AND parent_id = OBJECT_ID('dbo.Cliente')) > 0) DROP TRIGGER trgHistorico_Cliente

CREATE TRIGGER trgHistorico_Cliente ON dbo.Cliente -- Tabela que a trigger será associada

AFTER INSERT, UPDATE, DELETE AS

BEGIN

SET NOCOUNT ON

DECLARE

@Login VARCHAR(100) = SYSTEM_USER,

@HostName VARCHAR(100) = HOST_NAME(),

@Data DATETIME = GETDATE()

------- INÍCIO DA ALTERAÇÃO -------

DECLARE @SqlQuery VARCHAR(MAX)

DECLARE @TableSqlQuery TABLE (

EventType NVARCHAR(30),

[Parameters] INT,

EventInfo NVARCHAR(MAX)

)

INSERT INTO @TableSqlQuery

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

SET @SqlQuery = (SELECT TOP(1) EventInfo FROM @TableSqlQuery)

------- FIM DA ALTERAÇÃO -------

IF (EXISTS(SELECT * FROM Inserted) AND EXISTS (SELECT * FROM Deleted))

BEGIN

INSERT INTO dbo.Cliente_Log

SELECT @Data, @Login, @HostName, 'UPDATE', *, @SqlQuery -- ADICIONEI O @SqlQuery aqui

FROM Inserted

END

ELSE BEGIN

IF (EXISTS(SELECT * FROM Inserted))

BEGIN

INSERT INTO dbo.Cliente_Log

SELECT @Data, @Login, @HostName, 'INSERT', *, @SqlQuery -- ADICIONEI O @SqlQuery aqui

FROM Inserted

END

ELSE BEGIN

INSERT INTO dbo.Cliente_Log

SELECT @Data, @Login, @HostName, 'DELETE', *, @SqlQuery -- ADICIONEI O @SqlQuery aqui

FROM Deleted

END

E vamos testar novamente executando o comando abaixo:

INSERT INTO dbo.Cliente
VALUES('Dirceu', '1987-05-28', 0)

INSERT INTO dbo.Cliente
SELECT 
    'Resende' AS Nome, 
    '1987-05-28' AS Dt_Nascimento, 
    9999 AS Vl_Salario

INSERT INTO dbo.Cliente

VALUES('Dirceu', '1987-05-28', 0)

INSERT INTO dbo.Cliente

SELECT

'Resende' AS Name,

'1987-05-28' AS Dt_Nascimento,

9999 AS Vl_Salario

Result:

Como as 2 instruções SQL foram executadas juntas, no mesmo batch, o comando INPUTBUFFER capturou os 2 comandos de uma vez. Se eu tentar executar novamente, mas utilizando um GO para separar os blocos ou mesmo, executando separadamente, podemos observar que a trigger agora vai identificar corretamente os comandos de cada instrução:

INSERT INTO dbo.Cliente
VALUES('Dirceu - Teste 2', '1987-05-28', 0)
GO

INSERT INTO dbo.Cliente
SELECT 
    'Resende - Teste 2' AS Nome, 
    '1987-05-28' AS Dt_Nascimento, 
    9999 AS Vl_Salario
GO

INSERT INTO dbo.Cliente

VALUES('Dirceu - Teste 2', '1987-05-28', 0)

INSERT INTO dbo.Cliente

SELECT

'Resende - Teste 2' AS Name,

'1987-05-28' AS Dt_Nascimento,

9999 AS Vl_Salario

Result:

E se tiver outra trigger, em outra tabela, que está alterando a tabela Cliente e você não está conseguindo identificar de onde vem esse comando?

Primeiro, criaremos uma tabela com dados de funcionários:

IF (OBJECT_ID('dbo.Funcionario') IS NOT NULL) DROP TABLE dbo.Funcionario
GO

CREATE TABLE dbo.Funcionario (
    Id_Funcionario INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
    Nome VARCHAR(100)
)
GO

INSERT INTO dbo.Funcionario
(
    Nome
)
VALUES
    ( 'Dirceu' ), ('Resende')

IF (OBJECT_ID('dbo.Funcionario') IS NOT NULL) DROP TABLE dbo.Funcionario

CREATE TABLE dbo.Funcionario (

Id_Funcionario INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,

Name VARCHAR(100)

)

INSERT INTO dbo.Funcionario

(

Name

)

VALUES

( 'Dirceu' ), ('Resende')

Agora criarei uma trigger nessa nova tabela que atualiza a tabela Cliente caso o cliente e o funcionário tenham o mesmo nome:

CREATE TRIGGER trgFuncionario ON dbo.Funcionario -- Tabela que a trigger será associada
AFTER UPDATE AS
BEGIN
    
    SET NOCOUNT ON
   
    UPDATE A
    SET A.Nome = B.Nome
    FROM dbo.Cliente A
    JOIN Inserted B ON A.Nome = B.Nome
    
END
GO

CREATE TRIGGER trgFuncionario ON dbo.Funcionario -- Tabela que a trigger será associada

AFTER UPDATE AS

BEGIN

SET NOCOUNT ON

UPDATE A

SET A.Name = B.Name

FROM dbo.Cliente A

JOIN Inserted B ON A.Name = B.Name

END

Ao executar um simples comando de UPDATE, veremos que a trigger foi disparada nas duas tabelas

UPDATE dbo.Funcionario
SET Nome = 'Dirceu Resende'
WHERE Id_Funcionario = 1

UPDATE dbo.Funcionario

SET Name = 'Dirceu Resende'

WHERE Id_Funcionario = 1

Result:

Que legal! Mesmo o comando vindo de outra trigger, foi possível identificar a origem dessa alteração.

Mas e se você quiser mais detalhes da origem desse comando e identificar toda a cadeia de execução da trigger? Bom, neste caso, sugiro a leitura do artigo SQL Server – Como identificar e monitorar a execução de triggers.

Espero que vocês tenham gostado desse artigo e até a próxima 🙂
Abraços!

Subscribe to blog by email

Blog Views

Microsoft MVP

Post Archive

Categories

Recent posts

SQL Server – Como identificar a query que disparou uma trigger de DML numa tabela

Introduction

Creating the test base

Como identificar a query que disparou a trigger

You may also like...

Deixe uma respostaCancelar resposta

Introduction

Creating the test base

Como identificar a query que disparou a trigger

You may also like...

SQL Server – Como identificar timeout ou conexões interrompidas utilizando Extended Events (XE) ou SQL Profiler (Trace)

SQL Server – The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE

SQL Server – Cannot resolve the collation conflict between … in the equal to operation.

Deixe uma respostaCancelar resposta