Trigger/Event that fires when a Transaction finishes

Question

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.

Answer 1

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

Some clean-up

Followed by some clean-up:

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

DROP TABLE #to_log
go

... and, the processing happens here!

Now, the real deal.

Last but far from least, you are left with the job of writing the processing procedure.

Even if it were possible to intercept a COMMIT by a trigger, the resulting output to be processed would be very similar with this.

IF EXISTS(SELECT * FROM sys.procedures WHERE name = 'SP_2PROC') DROP PROCEDURE SP_2PROC
GO

CREATE PROCEDURE SP_2PROC AS
BEGIN
    -- Important!
    --
    -- Use WITH(NOLOCK) to avoid dead-locks
    --
    -- Do NOT use SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED here,
    -- unless you know what you are doing
    -- (this isolation level is often safe in other contexts)

    -- You, now, shall build your process up from here, based on the
    -- query below

    SELECT *
    FROM
        TBL_2PROC WITH(NOLOCK)
    WHERE session_id = @@SPID
END
GO