Solved

SQL trigger to Send an email when a record is submitted meeting certain parameters

Posted on 2014-10-21
9
188 Views
Last Modified: 2014-10-23
I have email set up within SQL Server. I am using SQL Server 2005. I want to put a trigger on a table that will send an email to an administrator when the record meets certain requirements. For example, a user inserts a record with a vacation destination of a certain country. So if destination = country1 or country2 or country3, send the email notification. Or if a record is submitted with the answer to a question being yes (it's a bit field). Is there an example of this somewhere?
0
Comment
Question by:dodgerfan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
9 Comments
 
LVL 17

Expert Comment

by:Barry Cunney
ID: 40396400
Hi Dodger
You can create an AFTER INSERT trigger on your table - this trigger could get the values from the internal 'inserted' table for the record just INSERTED and check if they match the criteria, and then send an e-mail if they do match the criteria.
The following is some sample code:
CREATE TRIGGER [dbo].[Matching_Insert_Travel_Table] ON [Test].[dbo].[Travel_Table]
AFTER INSERT
AS
DECLARE @RECORD_ID CHAR(32);
DECLARE @DESTINATION NVARCHAR(100);
DECLARE @ANSWER_1 BIT;

SELECT 
@RECORD_ID = i.[RECORD_ID] 
,@DESTINATION  = i.[DESTINATION]
,@ANSWER_1 = i.[ANSWER_1]
FROM inserted i


IF (@DESTINATION = 'Country1' OR @DESTINATION = 'Country2' OR @ANSWER_1 = 1)
	BEGIN

		EXEC master.dbo.xp_sendmail 
    			@recipients=N'DODGER@FANX.COM',
     			@message=N'Record which matches criteria inserted into Travel_Table: ' + @RECORD_ID ,
          		@subject=N'Matching record inserted into [Travel_Table]' ;
	
	END

Open in new window

0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40397255
You must keep in mind that SQL Server fires a trigger only once per statement no matter how many rows were affected.  Therefore, it's possible that a single trigger execution might have to send emails for multiple conditions.

Also, you want to do set-based processing rather than using variables.  Variables will be vastly less efficient, especially because you'll have to cursor thru them: yuck!

Here's a sample trigger using the proper techniques:


CREATE TRIGGER trigger_name
ON table_name
AFTER INSERT, UPDATE
AS
SET NOCOUNT ON;
IF OBJECT_ID('tempdb.dbo.#inserted') IS NOT NULL DROP TABLE #inserted
IF EXISTS(SELECT 1 FROM inserted i WHERE i.destination IN ('country1', 'country2', 'country3'))
BEGIN
    SELECT * INTO #inserted FROM inserted    
    EXEC msdb.dbo.sp_send_dbmail
        @recipients = 'email@ddress1;email@ddress2;',
        @subject = '...subject_text...',
        @body_format = 'TEXT',
        @importance = 'HIGH',
        @query = 'SELECT ... FROM #inserted i WHERE i.destination IN (''country1'', ''country2'', ''country3'')'
END --IF
IF EXISTS(SELECT 1 FROM inserted i WHERE i.answer_to_q1 = 1)
BEGIN
    IF OBJECT_ID('tempdb.dbo.#inserted') IS NULL
        SELECT * INTO #inserted FROM inserted
    EXEC msdb.dbo.sp_send_dbmail
        @recipients = 'email@ddress1;email@ddress2;',
        @subject = '...subject_text...',
        @body_format = 'TEXT',
        @importance = 'HIGH',
        @query = 'SELECT ... FROM #inserted i WHERE i.answer_to_q1 = 1'    
END --IF
GO
0
 

Author Comment

by:dodgerfan
ID: 40397669
Thanks for the help. I am getting the error: EXECUTE permission denied on object 'sp_send_dbmail',
database 'msdb', schema 'dbo'.
I'm having a hard time getting this straightened out. A record is added when a user inserts from a web page. That error is what I get back. I am using dbmail.
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40397722
Does your company have its own custom procedure for sending emails, rather than using the SQL system proc directly?  If so, maybe you can use that proc instead.

If not, you'll have to add permissions to the sp_send_dbmail proc and/or use perhaps "EXECUTE AS" on the trigger.  Even as DBA, I generally allow all app logins/users to use the send mail proc.
0
 

Author Comment

by:dodgerfan
ID: 40397741
I got the permissions fixed. It will execute now, but I am getting an error when I try to run it. I'm getting an Invalid Object name '#inserted'. error. I am not sure at what point it's happening in the trigger.

This is what I have:

ALTER TRIGGER trgsendmail
ON Itinerary
 AFTER INSERT
 AS
 SET NOCOUNT ON;
 IF OBJECT_ID('tempdb.dbo.#inserted') IS NOT NULL DROP TABLE #inserted
 IF EXISTS(SELECT 1 FROM inserted i WHERE i.countryid IN (1, 17, 301))
 BEGIN
     SELECT * INTO #inserted FROM inserted    
     EXEC msdb.dbo.sp_send_dbmail
         @recipients = 'myemail@ddress1;',
         @subject = '...subject_text...',
         @body_format = 'TEXT',
         @importance = 'HIGH',
         @query = 'SELECT * FROM #inserted i WHERE i.countryid IN (1,17,301)'
 END

The countryid is an integer, but for each one I'd like to put in the email the country name. So the text body would be "trip to France booked" or somethin g like that. Perhaps an if statement for each countryid?
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40397804
It's possible that user doesn't have authority to directly create tables in tempdb.  Again, that's something I normally allow our user ids to do.

We could instead create the text in the body of the trigger, and then attach it as standard message body.  I'll work on that and post it as soon as I can.
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 40397824
ALTER TRIGGER Itinerary_trg_sendmail
 ON Itinerary
  AFTER INSERT
  AS
  SET NOCOUNT ON;
  IF EXISTS(SELECT 1 FROM inserted i WHERE i.countryid IN (1, 17, 301))
  BEGIN
      DECLARE @body nvarchar(max)
      SELECT @body = STUFF((
          SELECT 'and Trip to ' + ISNULL(cm.country_name, CAST(i.countryid AS varchar(10))) + ' booked.'
          FROM inserted i
          LEFT JOIN country_master cm ON
              cm.countryid = i.countryid
          WHERE i.countryid IN (1,17,301)
          FOR XML PATH('')
      ), 1, 4, '')
      EXEC msdb.dbo.sp_send_dbmail
          @recipients = 'myemail@ddress1;',
          @subject = '...subject_text...',
          @body_format = 'TEXT',
          @importance = 'HIGH',
          @body = @body
  END
GO --end of trigger
0
 

Author Closing Comment

by:dodgerfan
ID: 40398372
That did it. It's working now. Thanks for the help!
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40399579
You're welcome!  That's a better approach overall as I think about it anyway, much less overhead than using a temp table.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question