Solved

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

Posted on 2014-10-21
9
183 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how the fundamental information of how to create a table.

832 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