SQL Server - Como identificar e monitorar a execução de triggers

Post Views 3,207 views

Reading time 5 minutes

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

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

)

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

Ds_Query VARCHAR(MAX)

)

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)

)

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

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

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

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

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

IF (EXISTS(SELECT TOP(1) NULL FROM sys.server_event_sessions WHERE [name] = 'trcMonitorTriggers'))

DROP EVENT SESSION [trcMonitorTriggers] ON SERVER;

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)

ALTER EVENT SESSION [trcMonitorTriggers] ON SERVER STATE = START

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

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

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

UPDATE dbo.Funcionario

SET Name = 'Dirceu Resende'

WHERE Id_Funcionario = 1

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)

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)

Result:

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

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