SqlServer Table Triggers

I am running a stored procedure from an after insert table trigger.  Its takes a while to complete.  My question is, will the application that inserts the table row wait for the trigger/stored procedure to complete?  My goal is to improve the application performance by the use of this trigger.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lcohanDatabase AnalystCommented:
If you EXEC SPname.. from the trigger it will 100% wait for that SP to complete in order to COMMIT that row change.
lcohanDatabase AnalystCommented:
This are the options I usually apply depending on the volumes (how intense OLTP that table takes) to be processed and how quick that SP action must be visible to the client UI:

1.  you could instead put some record in a queue table as this will be way faster than running that SP to completions then some SQL Job to process the queue and run the SP for each entry in that queue and mark it done so next step in that same job can clear (delete) these already processed rows from the queue.
2. more complex but you could create a SQL Job to run that SP action and destroy itself(delete) upon successful completion so this way trigger waits only for that SQL job creation which is usually faster that waiting on SP. I would chose this if that table takes large batch inserts OR random/rare one row here and there.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
My question is, will the application that inserts the table row wait for the trigger/stored procedure to complete?
Yes. What the trigger is doing to get so long to complete? You might need to improve the trigger performance.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.