Send email when data inserted in to table

1

Hi,

We need to send email to list of providers when data inserted in to table.

How I need to handle this ? Can you please suggest me on this..

Thank you.

2

A well written trigger on the table will do it. Example "B." in the following link sounds like what you need.
https://msdn.microsoft.com/en-us/library/ms189799.aspx

3

Create stored procedure and then after the insert complete you can call dbo.sp_send_dbmail with correct parameters
http://msdn.microsoft.com/en-us/library/ms190307.aspx

4

I have created Trigger which will be called when ever Insert/Update/Delete happen on "XYZ" table..

But my question is how I can write code with in the trigger

that should pull all the emails accounts from "abc" table and need to send an email to those .

Can you send me that code what I can written with in the trigger to send emails to all the providers.

Thank you.

5

Look at Jeff's link. Insert a query above the EXECUTE to pull the email addresses. If they are several records in table "abc" then you will need to combine to the send to variable. You may not want to do this in a trigger.

6

@djj55 hit the nail on the head here. If you need to send more than 1 email out, then don't do it in the trigger. Instead, write a "hit" to a "control" table and have a scheduled job send out the emails. But... even better, if you use Active Directory properly, you should have an AD Group that contains all the concerned citizens that you want to alert. Then you would have the trigger just send to the AD Group. The really cool part about that is that you don't ever have to change the trigger code to add or remove people from distribution and you don't need to maintain a table of people AND you only have to send 1 email instead of many. AD Groups is the correct way to resolve this problem.

If the "list of providers" aren't internal people in your Active Directory, then use the "save a hit" method I spoke of and have a scheduled job check the table for "hits" on a regular basis and have it send the emails (you know how to "loop" through a table, right?) rather than holding up transactions by doing it in the trigger.

Remember that if the trigger experiences any failure, the whole transaction will roll back, explicit or not.

7

You should never be sending email from your db server - that is a security no-no. Servers should only receive, not iniitate calls. This falls under the category of, just because you can, doesn't mean you should. Sending emails should be happening on the application end.