¿Cómo identificar la consulta que activó un activador DML en una tabla? Leí esta pregunta en un grupo de Telegram y decidí escribir un artículo al respecto.

Introducción

Como sabes, existen varias soluciones para implementar un registro de auditoría con cambios de datos en una tabla determinada:

Una de las soluciones más utilizadas para esto es el uso de activadores DML (aprenda cómo implementar esto). haciendo clic aquí)

En algunos escenarios, es importante identificar qué consulta terminó activando el disparador para quizás identificar el origen de los datos que se insertan desde SQL. Y eso es lo que aprenderemos a hacer en esta publicación.

Si desea obtener más detalles sobre el origen de este comando e identificar toda la cadena de ejecución del disparador, le sugiero leer el artículo. SQL Server: cómo identificar y monitorear la ejecución del disparador.

Creando la base de prueba

Para demostrar los códigos de este artículo, compartiré con ustedes el script que utilicé para ensamblar esta tabla y el activador utilizado para auditar los cambios. Usaré el mismo ejemplo que en el artículo. SQL Server: cómo crear un historial de cambios de datos para sus tablas (registros de auditoría).

Creación de tablas
Haga clic aquí para ver el código de creación 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
)

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

Creación de disparadores:
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()
        

    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

Hago algunos cambios a la tabla 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

Y podemos ver que la auditoría está funcionando como se esperaba:

Cómo identificar la consulta que activó el desencadenante

Ahora descubriremos cómo identificar la consulta que activó el desencadenante.

Para hacer esto, cambiaremos el disparador e incluiremos el comando INPUTBUFFER para capturar la consulta y almacenar esta información en la tabla de registro.

La sintaxis para usar el comando es la siguiente:

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)

Agregaremos el campo que almacenará la consulta en la tabla:

ALTER TABLE dbo.Cliente_Log ADD Ds_Query VARCHAR(MAX)

Y ahora cambiamos el disparador:
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()
        

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

Y probemos nuevamente ejecutando el siguiente comando:

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

Resultado:

Como las 2 sentencias SQL se ejecutaron juntas, en el mismo lote, el comando INPUTBUFFER capturó los 2 comandos a la vez. Si intento ejecutarlo nuevamente, pero usando un GO para separar los bloques o incluso ejecutándolo por separado, podemos ver que el disparador ahora identificará correctamente los comandos de cada instrucción:

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

Resultado:

¿Qué pasa si hay otro activador, en otra tabla, que está cambiando la tabla Cliente y no puede identificar de dónde proviene este comando?

Primero, crearemos una tabla con los datos de los empleados:

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

Ahora crearé un disparador en esta nueva tabla que actualiza la tabla Cliente si el cliente y el empleado tienen el mismo nombre:

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

Al ejecutar un comando ACTUALIZAR simple, veremos que el disparador se activó en ambas tablas.

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

Resultado:

¡Fresco! Si bien el comando provino de otro disparador, fue posible identificar el origen de este cambio.

Pero, ¿qué pasa si desea obtener más detalles sobre el origen de este comando e identificar toda la cadena de ejecución del disparador? Bueno, en este caso, te sugiero leer el artículo. SQL Server: cómo identificar y monitorear la ejecución del disparador.

Espero que hayas disfrutado este artículo y hasta la próxima 🙂
¡Abrazos!