Solved

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

Posted on 2014-10-21
9
171 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:ScottPletcher
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
 
LVL 69

Expert Comment

by:ScottPletcher
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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:ScottPletcher
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:
ScottPletcher 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:ScottPletcher
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Group by and order by clause 28 36
Trouble connecting to SqlServer database 4 34
SQL JOIN 6 39
SQL Inner Join Vs SubQueries 9 26
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

743 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now