I'm working on the following stored procedure but may need a little help.. Any help is appreciated.
My stored procedure is executed from a trigger and receives a parameter (QuoteID)
In my stored procedure
FOR EACH PART do the following
This scenario is screaming out for a solution that involves Service Broker and not a trigger. You could see a whole lot of trouble putting logic that spans databases in a trigger. Essentially the session that causes the trigger to fire will be waiting while the code in the trigger executes. The transaction will be open and causing locks and blocks while the code you show executes. If this execution gets held up for any reason then other transactions like your first one will be blocked. Your whole system could come to a standstill very quickly and very frequently.
Service Broker allows for two disconnected systems to play their part in a process without affecting the execution of the other in any detrimental way.
Triggers are a good way to apply business logic/rules/conditions to data but should be used carefully as they also have the ability to cause a system a lot of pain.
I know this isnt going to be an answer you were hoping for but if you are early enough in the design of this system that you can build it with Service Broker then you will be building a system that will be robust and will scale a lot better as the system(s) grow.
answered Aug 09 '12 at 08:58 AM