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