CODEWITHSUNDEEP
asp.netsqlsql-serversql-server-2008
Madusanka
Madusanka

Reputation: 3018

How to use after insert trigger to send e-mail

I would like to send an e-mail alert after inserting a new data to my sql server data table. How can i do this?

I have tried following trigger, but i'm no pretty sure about the functionality of it. 

CREATE TRIGGER dbo.sendMail 
   ON  dbo.staff_leaves
   AFTER INSERT
AS 
BEGIN

    SET NOCOUNT ON;
    EXEC msdb.dbo.sp_send_dbmail
          @to = '[email protected]', 
          @profile_name = 'default',
          @subject = 'New Row', 
          @body = 'Yep, they sure were.';

END
GO

Anyone please explain me how to do this.

Upvotes: 0

Views: 1706

Answers (1)

Saul
Saul

Reputation: 18041

You write that you are not pretty sure about the functionality but it is not clear what makes you unsure. Have you done a test run?

I suggest to check out the MSDN page for sp_send_dbmail which provides a nearly identical code sample and contains a bunch of other tips too. For example, MSDN recommends that:

Before use, Database Mail must be enabled using the Database Mail Configuration Wizard, or sp_configure.

and

Execute permissions (are required) for sp_send_dbmail default to all members of the DatabaseMailUser database role in the msdb database. However, when the user sending the message does not have permission to use the profile for the request, sp_send_dbmail returns an error and does not send the message.

Step-by-step description can be found at Database Mail set up in SQL Server 2008

Upvotes: 1

Related Questions