Trigger with xp_sendmail question

Question

Post reply

Trigger with xp_sendmail question

ehlinger

Hall of Fame

Points: 3585
More actions
July 27, 2004 at 1:22 pm

#109267

I am trying to create a trigger that sends an email based on if the values of the inserted record meet certain criteria. Here is what I have so far and it seems to be working fine.
CREATE TRIGGER EastPoorResults
ON Survey
FOR INSERT
AS
Declare @Q1 char,
@Q2 char,
@Q3 char,
@Q4 char,
@Q5 char,
@Q6 char
SELECT @Q1 = i.Q1,
@Q2 = i.Q2,
@Q3 = i.Q3,
@Q4 = i.Q4,
@Q5 = i.Q5,
@Q6 = i.Q6
FROM inserted i INNER JOIN
    CaseInfo c ON i.casenumber = c.CaseID
IF (@Q1 = '1') OR
    (@Q2 = '1') OR
    (@Q3 = '1') OR
    (@Q4 = '1') OR
    (@Q5 = '1') OR
    (@Q6 = '1') OR
    (@Q1 = '2') OR
    (@Q2 = '2') OR
    (@Q3 = '2') OR
    (@Q4 = '2') OR
    (@Q5 = '2') OR
    (@Q6 = '2')
BEGIN
EXEC master..xp_sendmail @recipients = 'Curt Ehlinger', @subject = 'Low Survey Results @q1'
I want it however to send an email that includes information from the inserted record and related information from another table. I've tried several different approaches but keep getting errors or emails with nothing in them at all. Here is the query that would contain the information for the email.
SELECT Survey.date, Survey.Q1, Survey.Q2, Survey.Q3, Survey.Q4, Survey.Q5, Survey.Q6, Survey.casenumber AS [Case Number], CaseInfo.AccountID AS [Account ID], CaseInfo.Company, CaseInfo.Name, CaseInfo.team AS [Team], CaseInfo.CloseRep AS [Close Rep], CaseInfo.CloseDate AS [Close Date]
FROM Survey INNER JOIN CaseInfo ON Survey.casenumber = CaseInfo.CaseID

Any guidance would be great. Thanks.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 1

Would you not want to create the second select using Inserted rather than Survery as the first condition in the join?

Shamless self promotion - read my blog http://sirsql.net

ehlinger Hall of Fame Points: 3585 More actions · Answer 2

You are correct, I want to create the second select using Inserted and I've attempted that as well without much success.

Andy-124155 SSC Veteran Points: 274 More actions · Answer 3

Speaking from past experience , I wouldn't have your email in the trigger at all. If the email fails for any reason, the original statement will fail since it will be part of the original transaction. Users will not be happy if they can't use the system because email is down. Can your trigger not insert a row into a new table instead, and have a SQL Server Agent checking the table every 5 minutes or so, and then sending the email from there (and then deleting that row in the table)?

Emails inside Triggers fire alarm bells in many DBA's eyes, and I'm now one of them.

Hope it helps.

Andy

michael blumenthal SSC Veteran Points: 248 More actions · Answer 4

Seconding Andy's post, our experience (yours may vary of course) of XP_Sendmail inside Triggers included many bad things right up to the SQLserver service terminating unexpectedly.

We moved to having the triggers insert message content into a table, and a separate scheduled job to actually send the messages, and (touch wood) it's been far more reliable.