Em um ambiente com muitas triggers sendo disparadas, muitas vezes é necessário conseguir identificar e monitorar a execução de triggers para analisar um determinado comportamento ou entender como um dado está sendo alterado.

Isso acaba ficando ainda mais complexo quando uma trigger altera dados de outra(s) tabela(s) e várias triggers são disparadas em sequência, aninhadamente, a partir de um único comando SQL.

É isso que você aprenderá nesse artigo.

Um das formas de conseguir identificar o comando DML que disparou uma trigger é alterar a própria trigger para capturar o comando SQL.

Veja como fazer isso nos artigos abaixo:

Exemplo:

Criação da base de testes

Para a demonstração desse post, compartilharei os scripts utilizados para que você possa testar no seu ambiente também.

Criação das tabelas
Clique aqui para visualizar o código 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
)
GO

-- 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,

    Ds_Query VARCHAR(MAX)
)
GO

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

Criação das triggers
Clique aqui para visualizar o código das triggers

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()
        

    ------- IDENTIFICAR A QUERY QUE DISPAROU A TRIGGER -------

    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

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

Como identificar e monitorar a execução de triggers

Agora que temos as tabelas e triggers criadas, vamos iniciar o nosso monitoramento, alterar alguns dados e testar se está tudo funcionando conforme o esperado.

Vamos criar um Extended Event (XE) para capturar a execução das triggers:

IF (EXISTS(SELECT TOP(1) NULL FROM sys.server_event_sessions WHERE [name] = 'trcMonitorTriggers'))
    DROP EVENT SESSION [trcMonitorTriggers] ON SERVER;
GO

CREATE EVENT SESSION [trcMonitorTriggers] 
ON SERVER 
ADD EVENT sqlserver.sp_statement_completed (
    ACTION (
        sqlserver.server_instance_name,
        sqlserver.session_id,
        sqlserver.client_app_name,
        sqlserver.client_hostname,
        sqlserver.[database_name],
        sqlserver.username,
        sqlserver.nt_username,
        sqlserver.session_nt_username,
        sqlserver.session_server_principal_name,
        sqlserver.server_principal_name,
        sqlserver.sql_text
    )
    WHERE ([object_type] = 'TRIGGER')
)
ADD TARGET package0.event_file (
    -- Lembre-se de criar a pasta antes de executar
    SET filename=N'C:\Traces\TriggerMonitor',
    max_file_size=(20),
    max_rollover_files=(10)
)
WITH (STARTUP_STATE=ON)
GO

ALTER EVENT SESSION [trcMonitorTriggers] ON SERVER STATE = START
GO

Vamos executar algumas alterações de dados para testar o nosso monitor de triggers:

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

INSERT INTO dbo.Cliente
SELECT 
    'Resende' 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)
GO

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

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

E agora vamos ler os dados coletados do Extended Event para visualizar o histórico de execução das triggers

DECLARE 
    @TimeZone INT = DATEDIFF(HOUR, GETUTCDATE(), GETDATE())
 
IF (OBJECT_ID('tempdb..#Events') IS NOT NULL) DROP TABLE #Events
;WITH CTE AS (
    SELECT CONVERT(XML, event_data) AS event_data
    FROM sys.fn_xe_file_target_read_file(N'C:\Traces\TriggerMonitor*.xel', NULL, NULL, NULL)
)
SELECT
    DATEADD(HOUR, @TimeZone, CTE.event_data.value('(//event/@timestamp)[1]', 'datetime')) AS eventDate,
    CTE.event_data
INTO
    #Events
FROM
    CTE

SELECT
    A.eventDate,
    xed.event_data.value('(data[@name="object_id"]/value)[1]', 'int') AS [object_id],
    OBJECT_NAME(xed.event_data.value('(data[@name="object_id"]/value)[1]', 'int')) AS [trigger_name],
    xed.event_data.value('(action[@name="session_id"]/value)[1]', 'int') AS session_id,
    xed.event_data.value('(action[@name="database_name"]/value)[1]', 'varchar(128)') AS [database_name],
    xed.event_data.value('(action[@name="nt_username"]/value)[1]', 'varchar(128)') AS nt_username,
    xed.event_data.value('(action[@name="session_server_principal_name"]/value)[1]', 'varchar(128)') AS session_server_principal_name,
    xed.event_data.value('(action[@name="client_hostname"]/value)[1]', 'varchar(128)') AS [client_hostname],
    xed.event_data.value('(action[@name="client_app_name"]/value)[1]', 'varchar(128)') AS [client_app_name],
    xed.event_data.value('(data[@name="nest_level"]/value)[1]', 'int') AS [nest_level],
    xed.event_data.value('(data[@name="statement"]/value)[1]', 'varchar(max)') AS [statement],
    TRY_CAST(xed.event_data.value('(//action[@name="sql_text"]/value)[1]', 'varchar(max)') AS XML) AS sql_text
FROM
    #Events A
    CROSS APPLY A.event_data.nodes('//event') AS xed (event_data)

Resultado:

Com esse script, você pode identificar cada comando executado dentro da trigger (coluna statement) ou o comando que disparou a trigger (coluna sql_text).

Caso você queira algo mais simples e resumido, para saber só quando as triggers foram disparadas, pode usar esse script aqui:

DECLARE 
    @TimeZone INT = DATEDIFF(HOUR, GETUTCDATE(), GETDATE())
 
IF (OBJECT_ID('tempdb..#Events') IS NOT NULL) DROP TABLE #Events
;WITH CTE AS (
    SELECT CONVERT(XML, event_data) AS event_data
    FROM sys.fn_xe_file_target_read_file(N'C:\Traces\TriggerMonitor*.xel', NULL, NULL, NULL)
)
SELECT
    DATEADD(HOUR, @TimeZone, CTE.event_data.value('(//event/@timestamp)[1]', 'datetime')) AS eventDate,
    CTE.event_data
INTO
    #Events
FROM
    CTE

SELECT DISTINCT
    A.eventDate,
    xed.event_data.value('(data[@name="object_id"]/value)[1]', 'int') AS [object_id],
    OBJECT_NAME(xed.event_data.value('(data[@name="object_id"]/value)[1]', 'int')) AS [trigger_name],
    xed.event_data.value('(action[@name="session_id"]/value)[1]', 'int') AS session_id,
    xed.event_data.value('(action[@name="database_name"]/value)[1]', 'varchar(128)') AS [database_name],
    xed.event_data.value('(action[@name="nt_username"]/value)[1]', 'varchar(128)') AS nt_username,
    xed.event_data.value('(action[@name="session_server_principal_name"]/value)[1]', 'varchar(128)') AS session_server_principal_name,
    xed.event_data.value('(action[@name="client_hostname"]/value)[1]', 'varchar(128)') AS [client_hostname],
    xed.event_data.value('(action[@name="client_app_name"]/value)[1]', 'varchar(128)') AS [client_app_name],
    xed.event_data.value('(data[@name="nest_level"]/value)[1]', 'int') AS [nest_level],
    xed.event_data.value('(//action[@name="sql_text"]/value)[1]', 'varchar(max)') AS sql_text
FROM
    #Events A
    CROSS APPLY A.event_data.nodes('//event') AS xed (event_data)
ORDER BY
    A.eventDate, nest_level

Resultado:

Nessa versão mais resumida, fica bem fácil identificar que um único update ativou 2 triggers ao observarmos as colunas de eventDate, nest_level e sql_text. Após o comando UPDATE, a trigger “trgFuncionario” foi disparada e como essa trigger faz um outro UPDATE na tabela “Cliente”, ela ativou a trigger “trgHistorico_Cliente”.

Agora ficou fácil identificar triggers sendo disparadas no ambiente, especialmente as triggers aninhadas como esse exemplo, onde um único comando pode acabar disparando várias triggers, ficando difícil de rastrear num ambiente grande.

Espero que tenham gostado dessa dica e até o próximo artigo!
Abraços.