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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
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', @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:
|
1 2 3 4 5 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
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'[email protected]', @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!
Chrissy is a Cloud and Datacenter Management & Data Platform MVP who has worked in IT for over 20 years. She is the creator of the popular SQL PowerShell module dbatools, holds a master's degree in Systems Engineering and is coauthor of Learn dbatools in a Month of Lunches. Chrissy is certified in SQL Server, Linux, SharePoint and network security. You can follow her on Twitter at @cl.
join chrissy on twitch
Thank you
nice and simple allowed me use create my own variation in a
couple of minutes
Thanks Crissy! This example was exactly what I needed…I just wasted 20 minutes on MSDN site…wish I’d found this sooner.
It worked great!!! Thank you so much
Thanks a lot ! The concept was superbly explained and was very useful for me.
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).”
I need some more clarification.
Thanks, worked for my project too. Nice clean code. Thanks!
hi i tried it and working 100 %
but how to create a trigger mail after delete or update?
This was great, thanks.
Great.
Works fine
Hey Sam,
Just do INSTEAD OF UPDATE or INSTEAD OF DELETE in the place of INSTEAD OF INSERT.
That should do the trick.
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= ‘[email protected]’, @body= @msg, @subject = ‘SQL Server Trigger Mail’, @profile_name = ‘TAlerts’
END
I get error near inserted.
Thanks, Nice and easy.
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
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 = ‘[email protected]’,
@display_name = ‘SQL Server Mailer’,
@mailserver_name = ‘smtp.gmail.com’
I’m working with SQL EXPRESS,is it the problem?
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…….
Thanks Nice and easy
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?
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 = ‘[email protected]’,
@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 = ‘[email protected]’,
@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?
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
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
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
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
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 !
Hi Chrissy
Excellent Post; it worked like a dream.
This was awesome, so quick and fast
AWESOME!
Only thing I had to add was “USE mydatabasename / GO” before the CREATE TRIGGER.
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
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
nice and helpfull article
thanks a lot
Great Stuff…Thank you very much.
Very helpfull article. Simple to understand and works great….
thanks very much
Murali
Thanks Murali,Muneer and Ajay……
Its a good example, thanks!
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
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..
Sure, i will help u out on this,shall send u the T-SQL very soon.
Great clean code sample! works great! Thanks so much! :)
Chris
Great, extremely helpful example.
Thank you. This has assisted me greatly in my application monitoring process.
why do i get “500 – internal server error” ? :(
Thanks for this site this site is help me in database programming god may you get your achivement.
thanku
this is the a very -very good example.
how to use trigger sqlserver 2008 & give any one example.
Awesome
Earlier, I wrote a trigger to be captured by code and then send a mail depending on the variable I got from SQL
Thanks
how to create Trigger for Select Statement in SQL Server 2005
My database keep giving me an error message execute permission denied on sendmail. no changes made to database and cancels my trnsaction. Can you please help.
Thanks
I tried Above Code..bt it's not working…
This is my code..
use Test_DB
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 = '[email protected]',
@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 ;
Create Table..
CREATE TABLE dbo.inventory (
item varchar(50),
price money
)
GO
Then I created Trigger…
Alter 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'myEmalID', @body= @msg, @subject = 'SQL Server Trigger Mail', @profile_name = 'DBMailProfile'
END
GO
–insert into inventory (item,price) values ('Vase',100)
–insert into inventory (item,price) values ('Oven',1000)
It's not working.. so help me.
Hi try this link is also best particle Example. http://codingresolved.com/discussion/74/dml-trigg…
This is the best, most simple syntax explanation of a basic trigger to send an email I’ve found in 2 days of googling. Thank you for not trying to overcomplicate things.
This is for one Recipeient sending email if you want more recipients then what to update in this trigger ??
you can try vsql-email (http://sql-email.com) it is very simple and saved me a lot of time