SQL Server - How to identify and monitor the execution of triggers

Views: 1.208 views

Reading Time: 5 minutes

In an environment with many triggers being fired, it is often necessary to be able to identify and monitor the execution of triggers to analyze a certain behavior or understand how data is being changed.

This ends up getting even more complex when a trigger changes data from another table(s) and multiple triggers are fired in sequence, nested, from a single SQL command.

That's what you'll learn in this article.

One of the ways to be able to identify the DML command that triggered a trigger is to change the trigger itself to capture the SQL command.

See how to do this in the articles below:

Example:

Test base creation

For the demonstration of this post, I will share the scripts used so that you can test in your environment as well.

Creating the tables
Click here to view the table code

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

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

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

Nome VARCHAR(100)

)

Creation of triggers
Click here to view the trigger code

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.Nome = B.Nome

FROM dbo.Cliente A

JOIN Inserted B ON A.Nome = B.Nome

END

How to identify and monitor the execution of triggers

Now that we have the tables and triggers created, let's start our monitoring, change some data and test that everything is working as expected.

Let's create an Extended Event (XE) to capture the execution of 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

Let's run some data changes to test our trigger monitor:

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

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

'1987-05-28' AS Dt_Nascimento,

9999 AS Vl_Salario

UPDATE dbo.Funcionario

SET Nome = 'Dirceu Resende'

WHERE Id_Funcionario = 1

And now let's read the data collected from the Extended Event to view the triggers execution history

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:

With this script, you can identify each command executed inside the trigger (statement column) or the command that triggered the trigger (sql_text column).

If you want something simpler and more concise, to know just when the triggers were fired, you can use this script here:

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