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.
Best Answer
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:
each potentially modifying the data on those tables, I would:
and, for the interception points, I would:
Below I will present:
Registry of events to be processed
To register records to be post-processed, I would design a single table such as:
Making sure no deprecated trigger be left alive
Before creating the actual triggers, we must be sure no old one be left:
Parameterizing targets
Before programmatically create the needed triggers, we have to declare which they will be:
Programmatically creating the needed triggers
Being the targets set, this script create the triggers:
Some clean-up
Followed by some clean-up:
... 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.