Need to trigger mail for an event

  • Hi

    I have a view which will fetch data from database and it contains mail ids also.

    When ever a new event is fetched, i need send the data to the mail ids on the same.

    Please help on this to achieve the same.

  • When ever a new event is fetched, i need send the data to the mail ids on the same.

    You can't add a trigger to a SELECT statement. How is the selection happening? If this were a stored procedure, you could process the rows, but you can't add an "event" to a view.

  • Hi

    Can we create a SP to achieve the requirement?

    Below is the query which i am using to fetch data.

    select * from euromaster..vw_vischeckinout

    And the result is below.

    visitId visitorId firstName lastName siteObjId location companyName purpose authorised siteName cardHolderId typeId empCode empName email reportManagerEmail additionalPersons additionalPersonDetails chkInOutId checkInPeriod checkOutPeriod startVisitDate endVisitDate visitType created lastModified operatorName chkin chkout visitorStatus

    1 1 Test Subu 2 test test company NULL Root Site 1 133 12345 Athi Athi test@test.com test1@test.com 0 NULL NULL NULL 9/12/2014 12:40 9/12/2014 23:59 NULL 9/12/2014 7:11 9/12/2014 7:11 admin1 NULL NULL 1

    2 2 Subu Test2 2 test test company NULL Root Site 1 133 12345 Athi Athi test@test.com test1@test.com 0 NULL NULL NULL 9/12/2014 12:45 9/12/2014 23:59 NULL 9/12/2014 7:16 9/12/2014 7:16 admin1 NULL NULL 1

    3 3 Test Subu3 2 test test company NULL Root Site 1 133 12345 Athi Athi test@test.com test1@test.com 0 1 9/12/2014 12:47 NULL 9/12/2014 12:46 9/12/2014 23:59 NULL 9/12/2014 7:16 9/12/2014 7:17 admin1 47:05.0 NULL 1

    4 4 Subu Test4 2 test test company NULL Root Site 1 133 12345 Athi Athi test@test.com test1@test.com 0 2 9/12/2014 13:18 NULL 9/12/2014 13:17 9/12/2014 23:59 NULL 9/12/2014 7:48 9/12/2014 7:48 admin1 18:41.0 NULL 1

    5 5 Test Subu5 2 test test company NULL Root Site 1 133 12345 Athi Athi test@test.com test1@test.com 0 3 9/12/2014 15:49 NULL 9/12/2014 15:49 9/12/2014 23:59 NULL 9/12/2014 10:19 9/12/2014 10:19 admin1 49:31.0 NULL 1

    6 6 Test Subu6 2 test test company NULL Root Site 1 133 12345 Athi Athi test@test.com test1@test.com 0 4 9/12/2014 15:58 NULL 9/12/2014 15:57 9/12/2014 23:59 NULL 9/12/2014 10:28 9/12/2014 10:28 admin1 58:40.0 NULL 1

    7 7 Test Subu7 2 test test company NULL Root Site 1 133 12345 Athi Athi test@test.com test1@test.com 0 5 9/12/2014 16:06 NULL 9/12/2014 16:06 9/12/2014 23:59 NULL 9/12/2014 10:36 9/12/2014 10:36 admin1 06:46.0 NULL 1

    Can you please help me on this ?

  • What do you mean by an "event"?

    How do you determine whether a row returned is a new event?

    I'd suggest a stored procedure that you put in a SQL Agent job scheduled to run every N minutes (based on how near real time you have to send the email) that loops over results returned and calls sp_send_dbmail. Pseudocode:

    Declare [variables to hold data from cursor];

    Declare mailCursor CURSOR FAST_FORWARD AS

    Select [columns] from [view] Where [criteria that shows new event];

    OPEN mailCursor;

    FETCH NEXT FROM mailCursor INTO [variables to hold data from cursor];

    WHILE @@FETCH_STATUS = 0

    BEGIN;

    Declare @Subject Varchar(1000) = [what ever the subject will be];

    Declare @Body varchar(8000) = [what ever the body will be]

    EXEC msdb.dbo.sp_send_dbmail [parameters = variables];

    Mark row as processed so you don't process the same row again next run.

    END;

    Another option is to use a Service Broker Queue where there is an activation procedure that sends the email, but I've never done that so I can't give you details. I know you'd have to write some code to get the event to the queue and the best way might be a trigger on the source table.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply