» Create a Basic SQL Server 2005 Trigger to Send E-mail Alerts

5Feb/0841

Create a Basic SQL Server 2005 Trigger to Send E-mail Alerts

For as many times as I have read about sending e-mails using SQL Server triggers, I've rarely come across actual code samples. After someone asked for a "Triggers for Dummies" example in a Facebook SQL group, I created the following example which uses a trigger to alert a manager that an expensive item has been entered into inventory.

First, if SQL Mail isn't enabled and a profile hasn't been created, we must do so.

--// First, enable SQL SMail

use master

go

sp_configure 'show advanced options',1

go

reconfigure with override

go

sp_configure 'Database Mail XPs',1

go

reconfigure

go


--//Now create the mail profile. CHANGE @email_address,@display_name and @mailserver_name VALUES to support your environment

EXECUTE msdb.dbo.sysmail_add_account_sp

@account_name = 'DBMailAccount',

@email_address = 'sqlserver@domain.com',

@display_name = 'SQL Server Mailer',

@mailserver_name = 'exchangeServer'


EXECUTE msdb.dbo.sysmail_add_profile_sp

@profile_name = 'DBMailProfile'


EXECUTE msdb.dbo.sysmail_add_profileaccount_sp

@profile_name = 'DBMailProfile',

@account_name = 'DBMailAccount',

@sequence_number = 1 ;

Now that SQL will support sending e-mails, let's create the sample table. This is not a useful or well designed table by any means -- it's just a simple example table:

CREATE TABLE dbo.inventory (

item varchar(50),

price money

)

GO

Now that SQL mail and the table are setup, we will create a trigger that does the following:

Creates an AFTER INSERT trigger named expensiveInventoryMailer on the inventory table. This means that the trigger will be executed after the data has been entered.
Checks for items being entered that have a price of $1000 or more
If there is a match, an email is sent using the SQL Mail profile we used above.

CREATE TRIGGER expensiveInventoryMailer ON dbo.inventory AFTER INSERT AS


DECLARE @price money

DECLARE @item varchar(50)


SET @price  = (SELECT price FROM inserted)

SET @item = (SELECT item FROM inserted)


IF @price >= 1000

	BEGIN

		DECLARE @msg varchar(500)

		SET @msg = 'Expensive item "' + @item + '" entered into inventory at $' + CAST(@price as varchar(10)) + '.'

		--// CHANGE THE VALUE FOR @recipients

		EXEC msdb.dbo.sp_send_dbmail @recipients=N'manager@domain.com', @body= @msg,  @subject = 'SQL Server Trigger Mail', @profile_name = 'DBMailProfile'

	END

GO

The only way to test a trigger is to add actual data, so let's do that here:
insert into inventory (item,price) values ('Vase',100)
insert into inventory (item,price) values ('Oven',1000)

Your email should arrive very quickly. If it doesn't, check the SQL Server mail log in SQL Management Studio by running SELECT * FROM sysmail_allitems.

Have fun!

Posted by: Chrissy Filed under: SQL Server Leave a comment

Comments (41) Trackbacks (1) ( subscribe to comments on this post )

Bryden
July 23rd, 2008 - 10:12

Thank you

nice and simple allowed me use create my own variation in a
couple of minutes

( REPLY )
Chris B.
September 30th, 2008 - 18:01

Thanks Crissy! This example was exactly what I needed…I just wasted 20 minutes on MSDN site…wish I’d found this sooner.

( REPLY )
Kimberly
October 7th, 2008 - 22:30

It worked great!!! Thank you so much

( REPLY )
Balasubramaniam K
October 19th, 2008 - 09:39

Thanks a lot ! The concept was superbly explained and was very useful for me.

( REPLY )
darshan thacker
November 2nd, 2008 - 12:03

hie…
m not getting sent mail from my db.
its giving me exception
“The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 2 (2007-03-08T00:49:29). Exception Message: Could not connect to mail server. (No connection could be made because the target machine actively refused it).”

( REPLY )
Lalit Kumar
November 16th, 2008 - 10:56

I need some more clarification.

( REPLY )
sihua
March 5th, 2009 - 13:48

Thanks, worked for my project too. Nice clean code. Thanks!

( REPLY )
sam
March 7th, 2009 - 11:53

hi i tried it and working 100 %

but how to create a trigger mail after delete or update?

( REPLY )
dabigo
March 7th, 2009 - 17:51

This was great, thanks.

( REPLY )
jakech
March 9th, 2009 - 04:10

Great.
Works fine

( REPLY )
Chrissy
March 13th, 2009 - 20:23

Hey Sam,
Just do INSTEAD OF UPDATE or INSTEAD OF DELETE in the place of INSTEAD OF INSERT.

That should do the trick.

( REPLY )
Priya
March 15th, 2009 - 07:24

Please tell me what is wrong with my code:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

– =============================================
– Author:
– Create date:
– Description:
– =============================================
ALTER TRIGGER [BirthdayMailer] ON [dbo].[Employee] FOR UPDATE
AS

DECLARE @BD varchar(50)
DeCLARE @FN VARCHAR(50)

SET @BD = (SELECT Birthdate FROM inserted)
SET @FN = (SELECT FirstName FROM inserted)

IF @FN = ‘Priya’
BEGIN
DECLARE @msg varchar(500)
SET @msg = ‘Upcoming Birthday “‘ + @BD + ‘” .Please check your database for more information’

EXEC msdb.dbo.sp_send_dbmail @recipients= ‘h@y.com’, @body= @msg, @subject = ‘SQL Server Trigger Mail’, @profile_name = ‘TAlerts’
END

I get error near inserted.

( REPLY )
Matt
March 20th, 2009 - 10:33

Thanks, Nice and easy.

( REPLY )
shep
March 24th, 2009 - 15:15

Thanks, here are some debugging queries:

–check to see if service broker is enabled
–SELECT is_broker_enabled FROM sys.databases WHERE name = ‘msdb’ ;

–check to see if database mail is started in msdb database
–use msdb go EXECUTE dbo.sysmail_help_status_sp ;

–start database mail if necessary
–EXECUTE dbo.sysmail_start_sp ;

–check status of mail queue
–sysmail_help_queue_sp @queue_type = ‘Mail’ ;

–check database mail event log
–use msdb select * from sysmail_event_log

–check database mail queue for status of all items
– use msdb select * from sysmail_allitems

( REPLY )
NEFI
April 15th, 2009 - 18:16

Hey Guys
I have problem sending mail, it doesn’t work, It runs but it dont sent
this is my cofiguration:
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = ‘DBMailAccount’,
@email_address = ‘nefihumberto@gmail.com’,
@display_name = ‘SQL Server Mailer’,
@mailserver_name = ‘smtp.gmail.com’

( REPLY )
NEFI
April 15th, 2009 - 18:17

I’m working with SQL EXPRESS,is it the problem?

( REPLY )
Maksuda
April 28th, 2009 - 12:13

Hi:
I tried the above code but no mail was sent.
From Log file I got the error message -
Could not connect to mail server. (No connection could be made because the target machine actively refused it 10.20.30.40:25).
4/24/2009 2:36:34 PM,sa

pls need help. I badly need help……….stuck here……pls…….

( REPLY )
Ram
April 29th, 2009 - 04:36

Thanks Nice and easy

( REPLY )
Lili
May 22nd, 2009 - 06:42

Is there an easy way to put conditions into the trigger? Like If a field = something send the email to emailgroup1 and if field=something else send the email to emailgroup2?

( REPLY )
Nathan Coson
May 24th, 2009 - 17:58

I keep getting a retrying/failed status. never tries to cmmunicate with the local mail that is programmed.
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = ‘SQLTest3′,
@email_address = ‘nathan@corsons.net’,
@display_name = ‘SQL Server Mailer’,
@mailserver_name = ’192.168.1.101′

EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = ‘SQLTest3′

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = ‘SQLTest3′,
@account_name = ‘nathan@corsons.net’,
@sequence_number = 1 ;

The query also states

Configuration option ‘show advanced options’ changed from 1 to 1. Run the RECONFIGURE statement to install.
Configuration option ‘Database Mail XPs’ changed from 1 to 1. Run the RECONFIGURE statement to install.

Any ideas?

( REPLY )
Nathan Corson
May 24th, 2009 - 21:33

OK got it to work but thru a test mail server that doesnt need authentication. How do i specify authentication for username and password? Thanks

( REPLY )
Nathan Corson
May 24th, 2009 - 21:39

OK got it to work but thru a test mail server that doesnt need authentication. How do i specify authentication for username and password? Also, is it possible to pull the email to send it to from a field in a separate table rather than be hard coded in the trigger? Thanks

( REPLY )
henry
June 8th, 2009 - 10:10

this is what I need:

a trigger to pass data from the table to an url address (to pass data from database to another database) I will be coping the information of 5, 6 fields; Can you help me ???

I will pay for your services.

thanks a lot

( REPLY )
Dennis Hatcher
June 15th, 2009 - 13:46

Excellent. May want to mention that the sysmail_allitems is in the msdb database. I just added the server to the select statement.

SELECT ‘sysmail_sentitems’ as TableName, * FROM msdb.dbo.sysmail_sentitems

SELECT ‘sysmail_faileditems’ as TableName, * FROM msdb.dbo.sysmail_faileditems

SELECT ‘sysmail_allitems’ as TableName, * FROM msdb.dbo.sysmail_allitems

SELECT ‘sysmail_unsentitems’ as TableName, * FROM msdb.dbo.sysmail_unsentitems

SELECT ‘sysmail_mailattachments’ as TableName, * FROM msdb.dbo.sysmail_mailattachments

( REPLY )
mahmoud
October 21st, 2009 - 07:12

hi dear Chrissy LeMaire
i have this problem
when i double-click on sqlmail the sqlmail configuration was show and wants to enter profile-name but i dont know what is the profile-name please help me !

( REPLY )
Keith Castle
October 22nd, 2009 - 15:41

Hi Chrissy
Excellent Post; it worked like a dream.

( REPLY )
Fatt Mike
October 23rd, 2009 - 10:15

This was awesome, so quick and fast

( REPLY )
Kevin P. Rice
November 12th, 2009 - 15:58

AWESOME!

Only thing I had to add was “USE mydatabasename / GO” before the CREATE TRIGGER.

( REPLY )
Stacy
December 7th, 2009 - 09:49

Hi Chrissy,
I am newbie to SQL mail..

Can you help me with my query

I am using SQL server 2005 I want to (when I execute a query result ) sent this result as a text message via email.
Help me and give me solution.

for example:-
select distinct amt, qty from xyz..sal where dateordered=getdate()-1

when it executed the result shown as
amt qty
20004.05 450
32843.90 200
and so on…

i want this result sent via emial to three differebt emails, every morning at 6a.m, I am using SQL server 2005.

Thank you

( REPLY )
Satnam Singh
January 19th, 2010 - 01:09

Consider an example where i have a table named student which has two columns named student_id and student_name.I need to send notification email to a particular user whenever any student name is inserted,updated or deleted.Please try the below tested code,i believe it will meet your requirements.

create table student
(
student_id int,
student_name varchar(100),
)

alter trigger trg_ins_upd_del on student
after insert,update,delete
as
begin

declare @student_name varchar(100)
declare @Newstudent_name varchar(100)
declare @MailSubject varchar(100)
declare @MailBody varchar(4000)

IF EXISTS(select INS.student_name from INSERTED INS inner join DELETED DEL on INS.student_name!=DEL.student_name
inner join student S on INS.student_name=S.student_name)
begin

select
@student_name=DEL.student_name
from DELETED DEL
inner join INSERTED INS on DEL.student_name!=INS.student_name

select @Newstudent_name=S.student_name
from student S
inner join INSERTED INS on S.student_name=INS.student_name
inner join DELETED DEL on S.student_name!=DEL.student_name

set @MailSubject=’Student Name Updated’

set @MailBody=’Hi,

Student named ‘+convert(varchar(100),@student_name)+’ has been updated.

Old Student Name:’+convert(varchar(100),@student_name)+’
New Student Name:’+convert(varchar(100),@Newstudent_name)+’

Regards,
XYZ
‘
print @MailBody

EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘Profile Name’,
@recipients =’Please enter the email address here’,
@body = @MailBody,
@subject = @MailSubject ;

end

else

/* Student name inserted */

IF EXISTS(select INS.student_name from INSERTED INS
inner join student S on INS.student_name=S.student_name)
begin

select @student_name = S.student_name from student S
inner join INSERTED INS on S.student_name = INS.student_name

set @MailSubject=’New Student Is Inserted’

set @MailBody=’Hi,

A new student named ‘+convert(varchar(100),@student_name)+’ has been inserted.

Thanks,
XYZ
‘

print @MailBody

EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘Profile Name’,
@recipients =’Please enter the email address here’,
@body = @MailBody,
@subject = @MailSubject ;

end

else

/* student is deleted */

IF EXISTS(select student_name from DELETED)
begin

select @student_name = student_name from DELETED

set @MailSubject = ‘Student Deleted’

set @MailBody = ‘Hi,

Student named ‘+convert(varchar(100),@student_name)+’has been deleted

Thanks,
XYZ
‘

print @MailBody

EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘Profile Name’,
@recipients =’Please enter the email address here’,
@body = @MailBody,
@subject = @MailSubject ;

end

end

( REPLY )
ajay : )
January 19th, 2010 - 04:02

nice and helpfull article
thanks a lot

( REPLY )
Muneer
January 26th, 2010 - 01:26

Great Stuff…Thank you very much.

( REPLY )
Murali Bala
February 15th, 2010 - 00:39

Very helpfull article. Simple to understand and works great….

thanks very much
Murali

( REPLY )
Satnam Singh
February 18th, 2010 - 00:11

Thanks Murali,Muneer and Ajay……

( REPLY )
gauree
April 30th, 2010 - 10:09

Its a good example, thanks!

( REPLY )
mphoben
May 21st, 2010 - 01:16

need some help here pliz..want to create an alert to the database administrator whenever the size of temporary space in the database is less than 20MB

( REPLY )
mphoben
May 21st, 2010 - 01:20

need some help here,i need to send an alert to Tony the database admistrator whenever the size of the temporary space in the database is less than 20MB..pliz help out..

( REPLY )
Satnam Singh
May 25th, 2010 - 05:32

Sure, i will help u out on this,shall send u the T-SQL very soon.

( REPLY )
Chris
June 3rd, 2010 - 18:04

Great clean code sample! works great! Thanks so much!

Chris

( REPLY )
StuJ
June 28th, 2010 - 09:43

Great, extremely helpful example.

Thank you. This has assisted me greatly in my application monitoring process.

( REPLY )
a sql user
July 13th, 2010 - 22:08

why do i get “500 – internal server error” ?

( REPLY )