none
stored procedure to send mail when a table is inserted or updated RRS feed

  • Question

  • Hi all, 

    I have a table called 'MyTable'. There is col 'CallNumber' as a primary key. Datatype is nvarchar. Now, I need a stored procedure to send a mail when a row is inserted/updated in that table. The mail should be sent with the call number. If multiple rows are inserted, it should send the multiple call numbers details in that mail. Im using sql server 2008 r2. Is this possible in sql server. Please help me. Thanks in advance.  

    Tuesday, May 1, 2012 5:14 AM

Answers

All replies

  • Below link addresses similar issue

    http://support.microsoft.com/kb/312839


    Regards,
    Ahmed Ibrahim
    SQL Server Setup Team
    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you.
    This can be beneficial to other community members reading the thread.

    Tuesday, May 1, 2012 5:27 AM
  • You can do it.

    Please see the below link.

    http://www.datasprings.com/resources/articles-information/creating-email-triggers-in-sql-server-2005


    ESHANI. Please click "Mark As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

    Tuesday, May 1, 2012 5:31 AM
  • Hi,

    You can create your stored procedure and then after the insert complete you can call dbo.sp_send_dbmail with correct parameters (make sure you already configure profile).

    http://msdn.microsoft.com/en-us/library/ms190307.aspx

    I hope this is helpful.


    Please Mark it as Answered if it answered your question
    OR mark it as Helpful if it help you to solve your problem
    Elmozamil Elamir Hamid

    MyBlog

    Tuesday, May 1, 2012 5:44 AM
  • Thanx guys. i hav done that using a trigger. But the problem is i had read somewhere that using send mail procedure inside trigger is not a good option. If mail has any issue like smtp server down, it will rollback the insert statement.  Also, the login which is inserting the records(Calling the trigger) should have sysadmin permissions.  Is that true?
    Tuesday, May 1, 2012 5:50 AM
  • Then you might consider inserting a record into a table from the trigger. Then have a scheduled process which does the job of monitoring the table and sending mails for each record. A suggestion.

    Welcome to MSDN Forums.
    Feel free to ask your questions and Please Note to Vote all helpful topics and Mark all answering posts.

    You Value Community's efforts and Community values your faith.
    - Arun Kumar Allu

    Tuesday, May 1, 2012 5:58 AM
  • Yes you are correct regarding sending emails from the trigger... A login should have at least EXECUTE permission on stored procedure msdb.dbo.sp_send_dbmail 

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Tuesday, May 1, 2012 7:21 AM
    Answerer
  • So I need a store procedure to do this. Can anyone help me. I need a SP to send a mail when a record is inserted or updated. 

    Tuesday, May 1, 2012 7:34 AM
  • And what is 100 rows were inserted and 1000 rows were updated? Still one email? Can you expand a little bit?

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Tuesday, May 1, 2012 7:38 AM
    Answerer
  • Actually in this table, updates and inserts are very less. if multiple rows are inserted, it should send multiple mails. 

    Tuesday, May 1, 2012 1:34 PM
  • 1.You can achive by many ways.First you need to configure database mail ,use the below link to do that

    http://blog.sqlauthority.com/2008/08/23/sql-server-2008-configure-database-mail-send-email-from-sql-database/

    2.Once you configure you need to call the SP EXEC sp_send_dbmail it is clearly expalined in the above url itself

     once you configure to call the SP when insert made on the table (MyTable).

    1.Create trigger on the table and call the SP to send mail.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful. By ganeshk

    Wednesday, October 10, 2012 11:29 AM
  • Then you might consider inserting a record into a table from the trigger. Then have a scheduled process which does the job of monitoring the table and sending mails for each record.

    If near real-time is acceptable, then triggerless solution is preferred. Just schedule the stored procedure every hour or so. Requires a flag column in the table IsEmailSent.


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner SQL Programming Using Microsoft SQL Server 2012

    Friday, October 19, 2012 11:21 AM
    Moderator