LIFEEXPERT
asked on
How does SQL Server trigger process utilization operate?
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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