We help IT Professionals succeed at work.

How does SQL Server trigger process utilization operate?

LIFEEXPERT
LIFEEXPERT asked
on
Hi experts,
  I have a large table that is utilized quite heavily. A task has been assigned that upon creation of certain IDs it send an email out notification. I have conceptuallized 2 possibilities.

1) An after Insert trigger calls a proc to send the email
OR
2) An after Insert trigger, inserts a record into an holding table with an insert trigger that sends the email.

My ignorance/derision is which will be more efficient. My thought is that after insertion when the trigger is executed... if it ran a Stored proc it MIGHT "hold up"  the table some way. I figured if I did a simple insert on another table and it execute the stored proc; it would be over and done with...the let that table take a hit on performance.

Thanks
Comment
Watch Question

Director
Commented:
Option 2 will be less efficient because its still all happening within the scope of the first trigger, and as it involves a second table and second trigger that will cost you.

If it truly is an issue performance wise to send the email within the trigger then the best thing I can think of is to insert the email into a holding table as you say, and then run a period task to check that table and send any emails.
David ToddSenior Database Administrator

Commented:
Hi,

Suggest that you use option 1b - Trigger to insert message in a queue, and a job that polls that queue every 15 seconds or so and sends the appropriate email.

Regards
  David