best approach for "trigger on commit"?

(I've searched the forum but haven't found anything that seems to address my specific question)

We have multiple tables that get updated as part of a transaction.

After all the data is added succesfully there is processing that needs

to be done, so we'd like to use a trigger to kick it off automatically. The schema can be thought of as including a "master report" table that gets updated once whenever a new report is submitted and multiple related tables each corresponding to different report sections. The PK-FK relations imply that the master report will be updated prior to the subsidiary sections, but the exact order of updates to the subtables is not defined.

I thought of attaching a trigger to the master report table but I can't find an approach or mechanism that would allow us to defer the triggered code until after all the tables have been updated.

It almost seems like I want a transaction trigger. Something that would be executed before or after a COMMIT TRAN. But the

post-processing code does not have to be deferred until after the

transaction is committed, it just mustn't be started until all the

tables participating in the transaction have been updated, otherwise

all the data needed won't be present.

There are several work-arounds that come to mind, but none are as

elegant as having a TRIGGER ON COMMIT capability. I'd be interested in

how other people have solved this problem.

Here's some of the work-arounds we came up with

1. Identify the last table to be updated, and place a trigger on that. Ugly for obvious reasons and very prone to breaking.

2. Create an "update history" table with a trigger on it. After the

actual transaction commits, a row should be added to this table by the

db user. Less ugly, but still not pretty.

3. Variation on #2 -- after an update transaction, have the database user call a stored procedure. Just as ugly as #2

4. Create a view that is used to write through to the underlaying tables, and have the trigger on the view. Unfortunately the large amount of data (approx 20 tables participate in one update transaction) makes this very unattractive. (Imagine an INSERT statement with 300 values!?)

Thanks in advance for suggestions and help

TerryPesonally, I would recommend any solution that does not use Triggers. Triggers are evil. They are a maintenance nightmare. After you finish your "transaction", you can call a stored proc that takes care of the rest of the updates which is a more controlled way of doing than doing it in a trigger. Do a simple google search on issues with triggers and you will be convinced enough to not use them.|||

We, I cannot agree with your feelings on triggers (Cursors are evil, not triggers. Triggers are just a bit difficult :) I do agree that this is not the place for a big trigger (considering how SQL Server triggers work.)

An alternative that I might suggest if this can be done asynchronously is that you have a job that queries your tables to see when the transaction is finished (either you check for the existance of data in all tables, or you write some sort of control row (possibly using triggers here) when you do each insert into the other tables. Then the process can check to see when all of the data for a "transaction" is completed and do your processing.

|||I can second Louis, triggers always had and will have a bad reputation because of the wrong implementation of users. Sure, in some cases they behave slow, they act syncronously (which can real nightmare if not considered) and are fired per statement not per row, so a bit implementation afford is needed here, but triggers can help your through the day for enforcing business rules which can′t be accomplished through any middle tier (if the database is directly access by several applications). So I would say that they are as evli as you force them to be :-)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||After the database is developed it may have data added by an unknown number of subcontractors. So it is important that the "api" be as simple as possible and that the database be as intelligent as possible. Building business logic into the database in the form of SP and triggers has clear advantages for us over an approach that depends on the goodwill of users to follow our suggested/required calling structure.

In other words, having a document that says "after inserting all of a report call SP after_insert_processing()" opens up the door for someone to forget to call the SP. Having a mechanism in place that detects the operation and automatically calls the SP is much more attractive to us.

Hmmm.....|||BTW thanks for all of the suggestions. We may go with a job that checks the time of the last update and determines from that if the post-insert processing should be done.|||

You can do something like below:

1. Create a tracking table

2. Insert a row from the tracking table for each operation on the other table(s) (Need not be at insert level. You can determine the granularity)

3. Have the insert trigger on the tracking table determine the matching conditions to fire the SP. This can be done by checking for count of specific number of operations per report for example. The post_insert_processing SP can be fired using an on-demand SQLAgent job so that it is asynchronous.

You could do away with the tracking table approach and simply do the check in every SP that inserts into the other table(s) also. Either way you can centralize the logic to check in a SP (by using simple queries against the required table(s)).

|||

My first question would be..."How do you currently ensure all information is updated correctly?"
My second question would be..."Is this a manual process that you want to include in your automation?"

It sounds as if there is some form of linear progression with a definitive end but you want to avoid this assumption for fear of change.

So...to satify your anxieties, create an independant action (the approach is insignificant).

Forget about the clever trickery or sly workaround, code a 101 solution to a 101 problem.

Adamus