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
fn_dblog()
and friends to read the transaction log, as discussed here.SET CONTEXT_INFO
/CONTEXT_INFO()
could be of help here.