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
2 comments:
this works fine, but let's say we want to execute @query in sp_send_dbmail. It hangs. anyone knows workaroud?
can you please post your code? I want to investigate it.
Post a Comment