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 =
'firstname.lastname@example.org' , @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
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).
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!
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.