December 9, 2009 at 5:02 am
Hello,
Is there a way to code a trigger so that executes and allows the transaction to complete immediately. We have a developer who wants to run an OS app as a trigger but I am worried about it holding up the transaction in our very busy OLTP system.
Thanks in advance.
December 9, 2009 at 6:40 am
Executing a program from inside a trigger is syncronous, meaning that the program needs to finish before the trigger can complete. An alternative would be to use Service Broker. Inside the trigger you send a message to a service queue and then the trigger completes. The actions taken by the Service Broker application are then run asyncronously from the application.
You didn't indicate what type of application the developer is trying to run from the trigger.
December 9, 2009 at 6:42 am
Not completely sure if I understand your requirement, but i think 'multithreading' is something you may want to look at. Create a new thread and leave it to execute. This can be done using CLR functions/stored procedure (may be CLR triggers, not done it though). Just trying to give a hint.
Hope this helps.
Edited: Ohh this is strange! I saw no one replied to this and thought I'd give it a shot. I can see Lynn's post now. I think that makes more sense than this!
---------------------------------------------------------------------------------
December 9, 2009 at 7:16 am
DO NOT run an outside executable from a trigger. Triggers are part of the transaction and there is no way I know of to commit the outer transaction (Insert/Update/Delete) and still run the trigger. Service Broker would be the right way to do it if it has to be done by SQL Server.
I would recommend that the application handle this by calling the next application once the database transaction has completed successfully. I'd keep it out of SQL Server if I could.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 9, 2009 at 7:44 am
Hello,
Thank you for your responses, they're much the same as mine when presented with this kind of proposal. This time the developers are wanting to invoke a com+ method to connect to the message queue of a remote server. This process will involve Windows domain authentication which I am worried will slow the transaction significantly on occasions.
Hopefully the Service Broker will offer a way through this. I'll shall investigate it further.
Thanks again.
December 9, 2009 at 10:45 am
For an example of an asynchronous trigger using Service Broker, see the demo code included in the ZIP file for my presentation, "The Top Ten Reasons Your Aren't Already Using Service Broker".
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply