En un entorno en el que se activan muchos desencadenantes, a menudo es necesario poder identificar y monitorear la ejecución de los desencadenantes para analizar un determinado comportamiento o comprender cómo se cambian los datos.

Esto termina volviéndose aún más complejo cuando un disparador cambia datos de otra(s) tabla(s) y se disparan varios disparadores en secuencia, anidados, desde un único comando SQL.

Eso es lo que aprenderá en este artículo.

Una de las formas de identificar el comando DML que activó un disparador es cambiar el disparador en sí para capturar el comando SQL.

Vea cómo hacer esto en los artículos siguientes:

Ejemplo:

Creación de la base de pruebas.

Para la demostración de esta publicación, compartiré los scripts utilizados para que puedas probarlos también en tu entorno.

Creación de tablas
Haga clic aquí para ver el código de la tabla

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

Creando desencadenantes
Haga clic aquí para ver el código de activación

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

Cómo identificar y monitorear la ejecución del disparador

Ahora que tenemos las tablas y los activadores creados, comencemos nuestro monitoreo, cambiemos algunos datos y probemos si todo funciona como se esperaba.

Creemos un Evento Extendido (XE) para capturar la ejecución de los disparadores:

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

Realicemos algunos cambios de datos para probar nuestro monitor de activación:

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

Y ahora leamos los datos recopilados del evento extendido para ver el historial de ejecución del activador.

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:

Con este script, puede identificar cada comando ejecutado dentro del disparador (columna de declaración) o el comando que activó el disparador (columna sql_text).

Si desea algo más simple y resumido, para saber solo cuándo se activaron los desencadenantes, puede usar este script aquí:

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:

En esta versión más corta, es muy fácil identificar que una sola actualización activó 2 activadores al mirar las columnas eventDate, nest_level y sql_text. Después del comando ACTUALIZAR se disparó el disparador “trgFuncionario” y como este disparador hace otro ACTUALIZAR en la tabla “Cliente”, activó el disparador “trgHistorico_Cliente”.

Ahora es fácil identificar los desencadenadores que se activan en el entorno, especialmente los desencadenadores anidados como este ejemplo, donde un solo comando puede terminar activando varios desencadenadores, lo que dificulta su seguimiento en un entorno grande.

Espero que te haya gustado este consejo y ¡nos vemos en el próximo artículo!
Abrazos.