patternsqlMinor

Trigger/Event that fires when a Transaction finishes

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

finishestriggerthattransactionwhenfiresevent

Problem

I have a query that pulls from about 15 different tables. I am looking to materialize this into a table that stores it in xml/json. (To improve performance.)

The problem I have is that these tables are updated by several processes. I am looking for a way to keep this in SQL Server if possible.

Ideally, I would love it if SQL Server had a trigger that would fire right before a transaction commits, so I could look at the tables and records that were affected and know if I need to update the "result" table.

Is there something like that in SQL Server?

NOTE: I have considered using the INSTEAD OF trigger, but I have no way of knowing the order of the tables in the transaction, so if the transaction updates all 15 tables, then I will be updating the "result" table 15 times for the same row.

Solution

Man, I'm terribly sorry.

I'm 'answering' stating that there's no simple solution as the one you devised.

You presented us a very interesting challenge.

I've been doing research and exercises in the past four hours to assure there's no way one can collect the aftermath of a committed transaction relying upon SQL Server engine by itself.

If you, please, could show us the grand view of the solution architecture, we would devise a least effort solution.

For instance, in the scenario:

one or two ADO.Net applications

one or two EntityFramework applications

one or two SSIS jobs

each potentially modifying the data on those tables, I would:

create a trigger for each table just to flag it has been tempered

create an intercption on each application calling a SP that would deal with the flagged data

and, for the interception points, I would:

on ADO.Net apps - supersede/override AdoDbConnection and rely on VS to quickly and safely refactor the code

on EF apps - override SaveChanges from DbContext

on SSIS jobs - include a call at the end of each one

Below I will present:

the schema of the table that will register events to be processed

a script that will generate the triggers for targeted tables

the snippet of what should be the processing stored procedures, called by the extension points listed above

Registry of events to be processed

To register records to be post-processed, I would design a single table such as:

IF EXISTS(SELECT * FROM sys.tables WHERE name = 'TBL_2PROC') DROP TABLE TBL_2PROC
go

CREATE TABLE TBL_2PROC(
    session_id     INT,          -- the session ID - it's expected each app transaction (or session) to have
                                 -- its own, exclusive, not shared, connection, even if pooled
    transaction_id BIGINT,       -- to assure grouping -> (session_id, transaction_id)
    event          VARCHAR(255), -- string stating an INSERT, UPDATE or DELETE
    tblName        VARCHAR(255), -- name of the table affected
    keyId          VARCHAR(255)  -- value of the (single) id column, converted to varchar
)
go

Making sure no deprecated trigger be left alive

Before creating the actual triggers, we must be sure no old one be left:

-- Clean-up existing ones ------------------------------------------------------
--------------------------------------------------------------------------------

DECLARE cCursor CURSOR LOCAL FAST_FORWARD FOR
SELECT name
FROM sys.triggers
WHERE name LIKE 'TR_2PROC%'
--
DECLARE @trName VARCHAR(255)
--
DECLARE @sql NVARCHAR(MAX)

OPEN cCursor
FETCH cCursor INTO @trName

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = 'DROP TRIGGER [' + @trName + ']'

    EXECUTE sp_executesql @sql

    FETCH cCursor INTO @trName
END
go

Parameterizing targets

Before programmatically create the needed triggers, we have to declare which they will be:

-- Parameterize the target tables and theirs key columns -----------------------
--------------------------------------------------------------------------------

CREATE TABLE #to_log(tblName VARCHAR(255), keyName VARCHAR(255))
go

INSERT INTO #to_log VALUES('SomeTable',      'Id')
INSERT INTO #to_log VALUES('SomeOtherTable', 'OtherId')
go

Programmatically creating the needed triggers

Being the targets set, this script create the triggers:

```
-- Setup triggers --------------------------------------------------------------
--------------------------------------------------------------------------------

DECLARE @tblName VARCHAR(255)
DECLARE @keyName VARCHAR(255)
--
DECLARE @sql NVARCHAR(MAX)
--

DECLARE cCursor CURSOR LOCAL FAST_FORWARD FOR
SELECT tblName, keyName
FROM #to_log

OPEN cCursor
FETCH cCursor INTO @tblName, @keyName

WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @triggerName VARCHAR(255)

SET @triggerName = '[TR_2PROC_' + @tblName + ']'

-- Drop if exists ----------------------------------------------------------

SET @sql = N'
IF EXISTS(SELECT * FROM sys.triggers WHERE name = ''' + @triggerName + ''')
DROP TRIGGER ' + @triggerName

EXECUTE sp_executesql @sql

-- Create ------------------------------------------------------------------

SET @sql = N'
CREATE TRIGGER ' + @triggerName + '
ON [' + @tblName + ']
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
DECLARE @session_id INT
DECLARE @transaction_id BIGINT

SELECT
@session_id = session_id,
@transaction_id = transaction_id
FROM sys.dm_tran_session_transactions
WHERE session_id = @@spid

INSERT INTO TBL_2PROC
SELECT
@session_id, @transaction_id,
CASE
WHEN del.[' + @keyName + '] IS NULL THEN ''INSERT''
ELSE ''UPDATE''
END,
''' + @tblName + ''',
CONVERT(VARCHAR(255),

Code Snippets

IF EXISTS(SELECT * FROM sys.tables WHERE name = 'TBL_2PROC') DROP TABLE TBL_2PROC
go

CREATE TABLE TBL_2PROC(
    session_id     INT,          -- the session ID - it's expected each app transaction (or session) to have
                                 -- its own, exclusive, not shared, connection, even if pooled
    transaction_id BIGINT,       -- to assure grouping -> (session_id, transaction_id)
    event          VARCHAR(255), -- string stating an INSERT, UPDATE or DELETE
    tblName        VARCHAR(255), -- name of the table affected
    keyId          VARCHAR(255)  -- value of the (single) id column, converted to varchar
)
go

-- Clean-up existing ones ------------------------------------------------------
--------------------------------------------------------------------------------

DECLARE cCursor CURSOR LOCAL FAST_FORWARD FOR
SELECT name
FROM sys.triggers
WHERE name LIKE 'TR_2PROC%'
--
DECLARE @trName VARCHAR(255)
--
DECLARE @sql NVARCHAR(MAX)

OPEN cCursor
FETCH cCursor INTO @trName

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = 'DROP TRIGGER [' + @trName + ']'

    EXECUTE sp_executesql @sql

    FETCH cCursor INTO @trName
END
go

-- Parameterize the target tables and theirs key columns -----------------------
--------------------------------------------------------------------------------

CREATE TABLE #to_log(tblName VARCHAR(255), keyName VARCHAR(255))
go

INSERT INTO #to_log VALUES('SomeTable',      'Id')
INSERT INTO #to_log VALUES('SomeOtherTable', 'OtherId')
go

-- Setup triggers --------------------------------------------------------------
--------------------------------------------------------------------------------

DECLARE @tblName VARCHAR(255)
DECLARE @keyName VARCHAR(255)
--
DECLARE @sql NVARCHAR(MAX)
--

DECLARE cCursor CURSOR LOCAL FAST_FORWARD FOR
SELECT tblName, keyName
FROM #to_log

OPEN cCursor
FETCH cCursor INTO @tblName, @keyName

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @triggerName VARCHAR(255)

    SET @triggerName = '[TR_2PROC_' + @tblName + ']'

    -- Drop if exists ----------------------------------------------------------

    SET @sql = N'
        IF EXISTS(SELECT * FROM sys.triggers WHERE name = ''' + @triggerName + ''')
        DROP TRIGGER ' + @triggerName

    EXECUTE sp_executesql @sql

    -- Create ------------------------------------------------------------------

    SET @sql = N'
        CREATE TRIGGER ' + @triggerName + '
        ON [' + @tblName + ']
        AFTER INSERT, UPDATE, DELETE
        AS
        BEGIN
            DECLARE @session_id     INT
            DECLARE @transaction_id BIGINT

            SELECT
                @session_id     = session_id,
                @transaction_id = transaction_id
            FROM sys.dm_tran_session_transactions 
            WHERE session_id = @@spid

            INSERT INTO TBL_2PROC
            SELECT
                @session_id, @transaction_id,
                CASE
                    WHEN del.[' + @keyName + '] IS NULL THEN ''INSERT''
                                                        ELSE ''UPDATE''
                END,
                ''' + @tblName + ''',
                CONVERT(VARCHAR(255), ins.[' + @keyName + '])
            FROM
                inserted ins
                    LEFT OUTER JOIN deleted del
                    ON del.[' + @keyName + '] = ins.[' + @keyName + ']

            INSERT INTO TBL_2PROC
            SELECT
                @session_id, @transaction_id,
                ''DELETE'',
                ''' + @tblName + ''',
                CONVERT(VARCHAR(255), del.[' + @keyName + '])
            FROM deleted del
            WHERE
                del.[' + @keyName + '] NOT IN(
                    SELECT ins.[' + @keyName + ']
                    FROM inserted ins
                )
        END'

    EXECUTE sp_executesql @sql

    FETCH cCursor INTO @tblName, @keyName
END
GO

-- Clean-up --------------------------------------------------------------------
--------------------------------------------------------------------------------

DROP TABLE #to_log
go

Context

StackExchange Database Administrators Q#105808, answer score: 2

Revisions (0)

No revisions yet.