Solved

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

Posted on 2014-10-21
9
185 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
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

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.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

685 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