x

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
more ▼

asked Apr 18 '12 at 04:55 PM in Default

jhowe gravatar image

jhowe
1.1k 47 56 60

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 '12 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 '12 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 '12 at 01:37 PM

Cyborg gravatar image

Cyborg
10.6k 36 39 45

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 '12 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 '12 at 01:51 PM

WilliamD gravatar image

WilliamD
25.8k 17 19 41

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 '12 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 '12 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 '12 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 '12 at 06:09 PM

JohnM gravatar image

JohnM
6k 1 3 7

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 '12 at 07:19 PM jhowe
What would the code look like for that?
Apr 18 '12 at 07:20 PM jhowe
Code for which part? The trigger or the SQL agent job?
Apr 19 '12 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 '12 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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x1816
x562
x114
x9

asked: Apr 18 '12 at 04:55 PM

Seen: 2264 times

Last Updated: Apr 24 '12 at 09:02 AM