question

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