CODEWITHSUNDEEP
triggerssql-server-2008-r2
diyoda_
diyoda_

Reputation: 5420

How triggers works internally in SQL Server

Please correct me if I am wrong.

What I know about triggers is that they are triggered by events (Insert, Update, Delete). So we can run a stored procedure etc.. in the trigger.

This will give the application a good responsiveness because the query that the user interacts with, is quite small and this "other" longer time taking stuffs are taken care by the server internally as a separate task.

But I do not know about how the the triggers are handled inside the server. What I exactly want to know is what would happen in scenarios as given below.

Take Insert after trigger. And take trigger is executing a longer stored procedure. Then in the middle of the trigger there can be another insert. What I want to know is what will happen to that second trigger. If possible can I make that second trigger ignore itself.

Upvotes: 1

Views: 6589

Answers (1)

diyoda_
diyoda_

Reputation: 5420

marc_s has given the correct answer. I will copy it for the sake of completeness.

TRIGGERS ARE SYNCHRONOUS

If you want to have a asynchronous functionality go for a SQL broker implimenation.

Triggers are triggered by events - and then they are executed - right now. Since you cannot control when and how often they are triggered, you should keep the processing in those triggers to an absolute minimum - I always try to make - at most - an entry into another table (an "Audit" table) or possibly put a "marker" row into a "command" table. But the actual processing of that info - running stored procedures etc. - should be left to an outside job - don't do extensive processing in a trigger! This will reliably KILL all your performance\responsiveness.

Upvotes: 2

Related Questions