best solution for trigger email alert help!

Hi all,

We work in the service industry and due to recent regulations, we have to be alerted if a customer books a combination of services together within a 24 hour period. Therefore I have suggested creating a trigger that will fire off an alert email if conditions are met etc. so bear with me i've created simple triggers before but nothing really advanced. Here is what i'm thinking of, i'm going to write it out exactly how i want it to work etc...

On insert or update on dbo.servicerequest table check to see if flight has been booked (serviceID = 1) and another service has been booked with it serviceID in 5,25,45 under the same customer record (dbo.customer, customerID) within a given time period i.e. (24 hours) (CreatedDateTime) >= DATEADD(dd, -1, GETDATE()) OR (CreatedDateTime)

Also I only want the alert to be sent out once, i don't want the agents to be bombarded with repeat emails. I'm thinking this may be too complicated for a trigger and I might need to run some sort of scheduled report to flag these situations up. I'm hoping one of the SQL gurus can advise on the possibilities! ;)

so this is what i've come up with so far, a job that runs a stored proc, please feel free to refactor and tell me if there's a better solution ! Thanks for your help.

 CREATE TABLE EmailAlert (
 CustomerID INT  ,
 ServiceRequestID INT ,
 ServiceID INT ,
 CompanyID INT ,
 ServiceCreatedBy INT ,
 CreatedDateTime NVARCHAR(50) ,
 AlertSent BIT
 CREATE PROC [dbo.][CheckForAndCreateEmailAlert]
 DECLARE @CustomerID INT ,
     @ServiceRequestID INT ,
     @SRN NVARCHAR(33) ,
     @ServiceID INT ,
     @ServiceDescription AS NVARCHAR(50) ,
     @CompanyID INT ,
     @CompanyName NVARCHAR(50) ,
     @FirstName AS NVARCHAR(50) ,
     @LastName AS NVARCHAR(50) ,
     @AgentID INT ,
     @AgentName AS NVARCHAR(50) ,
     @CreatedDateTime NVARCHAR(50) ,
     @CreateAlert AS BIT ,
     @AlertRecipient AS NVARCHAR(140) 
 SELECT  @CustomerID = CU.CustomerID ,
         @ServiceRequestID = SR.ServiceRequestID ,
         @SRN = SR.SRN ,
         @ServiceID = SR.ServiceID ,
         @ServiceDescription = SV.ShortName ,
         @CompanyID = CO.CompanyID ,
         @CompanyName = CO.Name ,
         @FirstName = COALESCE(CU.FirstName, '') ,
         @LastName = COALESCE(CU.LastName, '') ,
         @AgentID = SR.ServiceCreatedBy ,
         @AgentName = US.UserName ,
         @CreatedDateTime = SR.CreatedDateTime,
         @AlertRecipient = 'bla@bla.com' ,
         @AgentName = US.UserName ,
         @AgentID = SR.ServiceCreatedBy
 FROM    dbo.ServiceRequest SR
         INNER JOIN dbo.[User] US ON ServiceCreatedBy = UserID
         INNER JOIN dbo.[Service] SV ON SR.ServiceID = SV.ServiceID
         INNER JOIN dbo.ServiceAirTravel SRAT ON SR.ServiceRequestID = SRAT.ServiceRequestID
         INNER JOIN dbo.Customer CU ON SR.CustomerID = CU.CustomerID
         INNER JOIN dbo.CompanyLevel ON CU.CompanyLevelID = dbo.CompanyLevel.CompanyLevelID
         INNER JOIN dbo.Company CO ON dbo.CompanyLevel.CompanyID = CO.CompanyID
 WHERE   ( SR.ServiceID = 1 )
         AND ( SR.ServiceID = 5
               OR SR.ServiceID = 25
               OR SR.ServiceID = 27
               OR SR.ServiceID = 45
         AND FromCountry = 'United Kingdom'
         AND (CreatedDateTime) BETWEEN DATEADD(dd, -1,
                                                     AND     DATEADD(dd, +1,
                                                               GETDATE()) )
 IF @@ROWCOUNT > 0 SET @CreateAlert = '1' BEGIN
 INSERT INTO EmailAlert VALUES (@CustomerID, @ServiceRequestID, @ServiceID, @CompanyID, @AgentID, @CreatedDateTime) 
 WHERE NOT EXISTS (SELECT * FROM EmailAlert WHERE @CustomerID = CustomerID AND AlertSent = '1')
     if @CreateAlert = '1'
             declare @MailSubject as nvarchar(200)
             set @MailSubject = 'New ' + 
                 + ' Flight Alert ' 
             declare @MailBody as nvarchar(max)
             set @MailBody = 'A new Flight Package has been started.' + char(10)
                 + CHAR(10) + '      Agent: ' + @AgentName + CHAR(10)
                 + char(10) + '        SRN: ' + @SRN + char(10)
                 + '        Company Partner: ' + @CompanyName
                 + char(10) + '        Service: ' + @ServiceDescription
                 + char(10) + '        Customer: ' + @FirstName + ' '
                 + @LastName
             --print @MailBody
             exec msdb.dbo.sp_send_dbmail @profile_name = 'CRM EMail Services',
             --exec msdb.dbo.sp_send_dbmail @profile_name = 'CRM Mail Service',
                 @recipients = @AlertRecipient, @body = @MailBody,
                 @subject = @MailSubject ;
         UPDATE EmailAlert SET AlertSent = '1' WHERE @CustomerID = CU.CustomerID
more ▼

asked Apr 18, 2012 at 04:55 PM in Default

avatar image

1.1k 56 60 66

Your code example may well be the way you would like to go, but at present you will have issues with it.

Your query to fill the initial variables expects only one row to be returned by your query. As far as I can read it, you will get more than one row returned.

You would need to re-write this to use a cursor/loop to iterate over all your matching data and then send the mails.

Apr 23, 2012 at 07:10 AM WilliamD

Hi william would it be possible to email you about this for refactoring with a cursor/loop and how i would use service broker? i've never used service broker before and wouldn't have a clue where to start... i've sent you a linked in

Apr 24, 2012 at 09:01 AM jhowe
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

I would suggest you to stay away from trigger and use SQL Server Jobs, this hurts performance. You need to create a log table or event\Alert table which holds customer & servicerequest, Notified column with default 0 (1 = send and 0 = Pending). Handle the DMLs on dbo.servicerequest in a stored procedure, and if the DMLs meet the requirement of sending alerts insert the details to the "Alert" table.

To send emails, create sql server job that emails the records from "Alert" table, Update the Notified column to 1 meaning notification is send, Schedule this job to run every 15 minutes or so (As per business needs).

more ▼

answered Apr 19, 2012 at 01:37 PM

avatar image

10.8k 37 55 51

hmm... yes this seems like a better solution especially as the table that would have the trigger on it gets hammered... I'll get a query together and then paste my results so keep an eye open! ;)

Apr 19, 2012 at 01:54 PM jhowe
(comments are locked)
10|1200 characters needed characters left

Have you considered Service Broker for doing the processing asynchronously? The multiple events inside the time threshold are not to be blocked, just reported on, right?

You could then have a trigger on the events tables fire a "message" into a Service Broker queue. This could then run your analysis and decide on sending an email or not.

The main advantages here would be the trigger alerting Service Broker would be very fast / non-blocking and not slow down your application. The whole queue processing is already built into Service Broker, so you don't need to do that. The messages are processed in order and are transactionally safe (all the heavy lifting stuff that you have to build by hand otherwise).

On top of that, if you get into high transaction volumes, SB is built to scale out onto multiple sessions/servers.

The scenario you described is pretty much the exact use case for Service Broker.

more ▼

answered Apr 19, 2012 at 01:51 PM

avatar image

26.2k 18 35 48

I think using service broker might be overkill for this as the transaction volumes are very low... I'll paste what I have so far tomorrow! ;)

Apr 19, 2012 at 08:58 PM jhowe

I'm not sure that volume would have much to do with using service broker per se. The main thing to recognise is that you have to try and keep your trigger as short as possible - offloading to anything else, even a manual queue, would be the way to go.

I mentioned service broker here, because it already implements queuing for you. This means one less piece of code to "worry" about.

It also exposes you to an extremely under-rated but amazingly powerful part of SQL Server. This alone could be an eye-opener and provide you with a new tool for your belt.

Apr 20, 2012 at 08:27 AM WilliamD

The scenario described in the question screams for a Service Broker solution. I note you say the transaction volumes are low. So what? Surely you hope that they will increase? If you use Service Broker you wont have any worries about increases in activity making your application and business processes slow. Having its disconnected/asynchronous architecture means you dont have any risk of it locking your whole system. With a trigger there is a risk introduced into the transaction that it will be delayed.

If it was my project I wouldnt consider anything but SB.

Apr 21, 2012 at 12:37 PM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left

Just curious, what's the regulation say about when the alert has to occur? Right away after each transaction or can it happen on a daily basis?

Not knowing how volatile your system is, I would look at writing a job that would gather the a list of customers who purchased a combination of services within that 24 hour period and then email it out once a day. That way, your agents would get a detailed concise list of customers once a day versus potentially one every minute.

You also write a SSRS report to make a report pretty and allow the agents to pull a report for any given day if you'd like.

Just my .02 on it though. I'm sure that the other wizards on this forum will have some ideas for you. ;-)

Hope this helps!

more ▼

answered Apr 18, 2012 at 06:09 PM

avatar image

14k 3 7 14

So they book a flight, and then as soon as that same customer books one of the other services in the time periods specified it fires an email straight away...

Apr 18, 2012 at 07:19 PM jhowe

What would the code look like for that?

Apr 18, 2012 at 07:20 PM jhowe

Code for which part? The trigger or the SQL agent job?

Apr 19, 2012 at 12:47 PM JohnM

code for both? ;) I just need an idea of what to do end to end... a couple of approaches would be good if anyone else decides to look at my post!

Apr 19, 2012 at 01:03 PM jhowe
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Apr 18, 2012 at 04:55 PM

Seen: 3472 times

Last Updated: Apr 24, 2012 at 09:02 AM

Copyright 2016 Redgate Software. Privacy Policy