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

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?
dodgerfanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Barry CunneyCommented:
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
Scott PletcherSenior DBACommented:
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
dodgerfanAuthor Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Scott PletcherSenior DBACommented:
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
dodgerfanAuthor Commented:
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
Scott PletcherSenior DBACommented:
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
Scott PletcherSenior DBACommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dodgerfanAuthor Commented:
That did it. It's working now. Thanks for the help!
0
Scott PletcherSenior DBACommented:
You're welcome!  That's a better approach overall as I think about it anyway, much less overhead than using a temp table.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.