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.

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:

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.

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 PowerShell MVP who has worked in IT for nearly 20 years, and currently serves as a Sr. Database Engineer in Belgium. Always an avid scripter, she attended the Monad session at Microsoft’s Professional Developers Conference in Los Angeles back in 2005 and has worked and played with PowerShell ever since. Chrissy is currently pursuing an MS in Systems Engineering at Regis University and helps maintain RealCajunRecipes.com in her spare time. She holds a number of certifications, including those relating to SQL Server, SuSE Linux, SharePoint and network security. She recently became co-lead of the SQL PASS PowerShell Virtual Chapter. You can follow her on Twitter at @cl.

Posted in SQL Server
52 comments on “Create a Basic SQL Server 2005 Trigger to Send E-mail Alerts
  1. Bryden says:

    Thank you

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

  2. Chris B. says:

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

  3. Kimberly says:

    It worked great!!! Thank you so much

  4. Balasubramaniam K says:

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

  5. darshan thacker says:

    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).”

  6. Lalit Kumar says:

    I need some more clarification.

  7. sihua says:

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

  8. sam says:

    hi i tried it and working 100 %

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

  9. dabigo says:

    This was great, thanks.

  10. jakech says:

    Great.
    Works fine

  11. Chrissy says:

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

    That should do the trick.

  12. Priya says:

    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.

  13. Matt says:

    Thanks, Nice and easy.

  14. shep says:

    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

  15. NEFI says:

    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’

  16. NEFI says:

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

  17. Maksuda says:

    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…….

  18. Ram says:

    Thanks Nice and easy

  19. Lili says:

    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?

  20. Nathan Coson says:

    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?

  21. Nathan Corson says:

    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

  22. Nathan Corson says:

    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

  23. henry says:

    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

  24. 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

  25. mahmoud says:

    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 !

  26. Keith Castle says:

    Hi Chrissy
    Excellent Post; it worked like a dream.

  27. Fatt Mike says:

    This was awesome, so quick and fast

  28. AWESOME!

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

  29. Stacy says:

    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

  30. Satnam Singh says:

    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

  31. ajay : ) says:

    nice and helpfull article
    thanks a lot

  32. Muneer says:

    Great Stuff…Thank you very much.

  33. Murali Bala says:

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

    thanks very much
    Murali

  34. Satnam Singh says:

    Thanks Murali,Muneer and Ajay……

  35. gauree says:

    Its a good example, thanks!

  36. mphoben says:

    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

  37. mphoben says:

    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..

  38. Satnam Singh says:

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

  39. Chris says:

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

    Chris

  40. StuJ says:

    Great, extremely helpful example.
    Thank you. This has assisted me greatly in my application monitoring process.

  41. a sql user says:

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

  42. Thanks for this site this site is help me in database programming god may you get your achivement.
    thanku

  43. rahul rawade says:

    this is the a very -very good example.

  44. rahul rawade says:

    how to use trigger sqlserver 2008 & give any one example.

  45. Saud says:

    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

  46. Bhausaheb Funde says:

    how to create Trigger for Select Statement in SQL Server 2005

  47. brandon says:

    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

  48. Mahendra says:

    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.

  49. waqas says:

    Hi try this link is also best particle Example. http://codingresolved.com/discussion/74/dml-trigg

  50. Chad says:

    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.

  51. Mujahid Rafiq says:

    This is for one Recipeient sending email if you want more recipients then what to update in this trigger ??

  52. doc_m13 says:

    you can try vsql-email (http://sql-email.com) it is very simple and saved me a lot of time

4 Pings/Trackbacks for "Create a Basic SQL Server 2005 Trigger to Send E-mail Alerts"
  1. […] myst try this out: Sending mail from sql server trigger […]

  2. […] 本文转自:http://blog.netnerds.net/2008/02/create-a-basic-sql-server-2005-trigger-to-send-e-mail-alerts/ […]

Leave a Reply

Your email address will not be published. Required fields are marked *

*