Email from INSERT trigger with msdb.dbo.sp_send_dbmail in SQL Server 2005

Sometime we need to send email from database based on specific condition. How can we cater this need?

I have just finished this task in one of our databases. I have to send an email to specific email address when particular records getting inserted in specific table. Trigger is a best option to check whether an inserted record falls under our criteria or not. Moreover I have used msdb.dbo.sp_send_dbmail to send an email.

Note: msdb.dbo.sp_send_dbmail will work only if you have enable database mail, as msdb.dbo.sp_send_dbmail will send an email from the profile set in database email.

Let us create one example to do so.

USE [AdventureWorks]

GO

–create table

USE [adventureworks]

GO

CREATE TABLE [dbo].[emps](

[Name] [varchar](50),

[Dept] [varchar](10),

[Company] [varchar](15)

) ON [PRIMARY]

GO

–insert records

INSERT INTO emps

SELECT ‘RITESH’,‘MIS’,‘ECHEM’ UNION ALL

SELECT ‘Rajan’,‘MIS’,‘mar’

–create one trigger which will check inserted record from schedo table “INSERTED”

–and send an email with msdb.dbo.sp_send_dbmail

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TRIGGER [TR_EMPS_INSERT]

ON [emps]

FOR INSERT

AS

DECLARE @Dept varchar(6)

DECLARE @Name VARCHAR(10)

DECLARE @Bod VARCHAR(MAX)

DECLARE @Sub VARCHAR(100)

BEGIN

SELECT @Dept=Dept,@Name=Name FROM INSERTED

IF @Dept=‘MIS’

BEGIN

SELECT @Sub=‘New employee in MIS department’

SELECT @Bod=‘Hello, <BR><BR>New Employee (‘+ @Name +‘) has been recruted in MIS department ‘

EXEC msdb.dbo.sp_send_dbmail @recipients=‘Rits4Friends@gmail.com’,@copy_recipients=‘ritesh_a_shah@yahoo.com’,

@subject = @SUB,

@body = @BOD,

@body_format = ‘HTML’;

END

END

Happy Triggering!!!!

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Advertisements

4 Responses to “Email from INSERT trigger with msdb.dbo.sp_send_dbmail in SQL Server 2005”

  1. Anonymous Says:

    this works fine, but let’s say we want to execute @query in sp_send_dbmail. It hangs. anyone knows workaroud?

  2. Anonymous Says:

    this works fine, but let’s say we want to execute @query in sp_send_dbmail. It hangs. anyone knows workaroud?

  3. Ritesh Shah Says:

    can you please post your code? I want to investigate it.

  4. Ritesh Shah Says:

    can you please post your code? I want to investigate it.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: