Using a Trigger to Call a Store Proc

Question

Using a Trigger to Call a Store Proc

ExhibitA

Ten Centuries

Points: 1074
More actions
August 21, 2018 at 5:54 am

#372093

Hi all,
I have a problem I trying to solve and would appreciate nay input.
I have an InfoPath form (don't ask) that is connected to a table in SQL.
What I am trying to acheive:
I have created an insert trigger on the table that is designed to email specific people if they are selected form the drop down on the infopath
The triggert invoked a stored procedure that emails the specific people.
How do I add additional IF statements to email different people. I hope this makes sense.

USE WorkDatabase
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER Feedback_Email
ON [dbo].[T_Feedback]
AFTER INSERT
AS
IF (SELECT Leader_Name from [dbo].[T_Feedback])='Jon Doe'
BEGIN ------- Do I add aditional IF clauses here?
DECLARE @xml NVARCHAR(MAX) = CAST((

                                SELECT td=rowid
                                ,''
                                ,  td= TeamName
                                , ''
                                ,     td= FeedbackProvider
                              , ''
                                ,     td= Howwasitidentified
                                , ''
                                ,     td=TypeofFeedback
                                , ''
                                ,  td=CustomerID
                                , ''
                                ,  td=DateFeedbackReceived
                                ,''
                                ,     td=Overview
                            from
                            (
                                    select
                                             ROWID
                                            ,TeamName
                                            ,Feedback_Provider as 'FeedbackProvider'
                                            ,How_was_it_identified as 'Howwasitidentified'
                                            ,Type_of_Feedback as 'TypeofFeedback'
                                            ,CustomerID
                                            ,Date_Feedback_Received as 'DateFeedbackReceived'
                                            ,Overview
                                            from
                                            workdatabase.[dbo].[T_Feedback]
                                ) A
FOR XML PATH('tr'), ELEMENTS) AS NVARCHAR(MAX))
DECLARE @TABLEHTML NVARCHAR(MAX);
SET @TABLEHTML =
N'<H1><font size = "3" color="black">Hello, </font></H1>' +
N'<H2><font size = "3" color= "black">Please find the details below of remedial feedback that was identified.</font></H2>' +
N'<table border="1" style type="text/css">' +
N'<tr>RowID</th></td>' +
N'<th>TeamName</th></td>' +
N'<th>FeedbackProvider</th></td>' +
N'<th>Howitwasidentified</th></td>' +
N'<th>TypeofFeedback</th></td>' +
N'<th>CustomerID</th></td>' +
N'<th>DateFeedbackReceived</th></td>' +
N'<th>Overview</th></td>' +
@xml+
N'</table>' +
N'<H3><font size="3" color="black"> Please use the link below to record the outcome of the feedback Provided using the RowID provided (InfoPath)' + '</font></H3>' + --+ @maxID +
'<a href="Z:\infopath">CLICK FOR Infopath</a></font></H4>'+
N'<H4><font size="3" color="black"> Kind Regards, </font></H5>';
EXEC WorklDatabse.dbo.usp_EmailProgress
@RECIPIENTS= Jon.Doe@company.co.uk,
@COPY_RECIPIENTS= ' Joe.Bloggs@company.co.uk,
@Reply_to = 'Joe.Bloggs@company.co.uk' ,
@subject = 'Remedial Action',
@body = @tablehtml,
@body_format = 'html'
END

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

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

Thom A SSC Guru Points: 99130 More actions · Answer 1

Honestly, don't send email from triggers, they cause far more problems then they "solve". If a trigger fails, for what ever reason, then the INSERT/UPDATE/DELETE will also fail (and therefore be rolled back). This means that if your mail server is down, for example, no one will be able to INSERT into your table.

You would be far better inserting the data you need into a pooling table (that can be handled in the TRIGGER), or similar, and then emailing the data from there on a regular basis. You'll likely need to use a CURSOR to loop through the recipients to email, if you need to email them different data. Don't be concerned about doing that, CURSORs aren't inherently bad it's must that a lot of people make the mistake of using them when they shouldn't.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

ExhibitA Ten Centuries Points: 1074 More actions · Answer 2

ExhibitA

Ten Centuries

Points: 1074

August 22, 2018 at 2:30 am

#2002801

Thanks for your reply

Really helpful

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 3

I completely agree with Thom about sending emails. I tend to use a table for the target and then poll it as well, because this gives me a record, and if something breaks, I can fix it. The other solution is a queue, ala Service Broker, but I think that's more complex than it needs to be.

Build a job to read the table, if there are entries, loop through them and call your email proc. One note from someone that's done this, I'd limit the potential emails. If I schedule the job to run every minute, I might cap the number of entries at some value, like 100. This prevents a long running job, and also lets me stop/disable this without having a large queue of stuff in process.