question

jhowe avatar image
jhowe asked

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) <= DATEADD(dd, +1, GETDATE()) if this returns true, select customer and servicerequest information and create/insert into body of email and email it. If it returns false, do nothing. 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] AS 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, GETDATE()) 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') END if @CreateAlert = '1' begin 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 end
sql-server-2008sql-server-2008-r2triggerdatabase-mail
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WilliamD avatar image WilliamD commented ·
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.
0 Likes 0 ·
jhowe avatar image jhowe commented ·
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
0 Likes 0 ·
Cyborg avatar image
Cyborg answered
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).
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

jhowe avatar image jhowe commented ·
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! ;)
0 Likes 0 ·
JohnM avatar image
JohnM answered
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!
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

jhowe avatar image jhowe commented ·
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...
0 Likes 0 ·
jhowe avatar image jhowe commented ·
What would the code look like for that?
0 Likes 0 ·
JohnM avatar image JohnM commented ·
Code for which part? The trigger or the SQL agent job?
0 Likes 0 ·
jhowe avatar image jhowe commented ·
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!
0 Likes 0 ·
WilliamD avatar image
WilliamD answered
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.
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image Fatherjack ♦♦ commented ·
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.
1 Like 1 ·
jhowe avatar image jhowe commented ·
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! ;)
0 Likes 0 ·
WilliamD avatar image WilliamD commented ·
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.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.