Email data from several tables which is joined with inserted table

Question

I have four tables:

Activity:

ActivityID(PK)    ActivityName      CustomerID(FK)  UserId(FK)
1                Lead Gen 1st           50         U1
2                Lead Gen 2nd           60         U2

Customer:

CustomerID(PK)   CustomerNumber  CustomerName
50                  C0150          cust50 ltd
60                  C0160          cust60 ltd

User:

UserID(PK)  UserName     Email        
U1           Mr. X      X@cat.com        
U2           Mr. Y      Y@cat.com

UserActivity:

UserActivityID(PK)  UserID(FK)     ActivityID(FK) 
888                   U1             1
889                   U2             2

I want to send an email (i.e. Email:X@cat.com) to the users related to the activity (i.e. ActivityId:1) if any insert happens in Activity Table (SQL Server 2008-R2).

The email body should contain the ActivityId, ActivityName, CustomerNumber and CustomerName.

The trigger has to do the above mentioned and the result should be like this in the email:

ActivityID:1, ActivityName:Lead Gen 1st created for  CustomerNumber: C0150 & CustomerName: cust50 ltd

Here is my code:

CREATE TRIGGER [dbo].[Activity_Insert_Mail_Notification]
  ON [dbo].[Activity]
AFTER INSERT
AS
BEGIN

  DECLARE @ActivityID varchar(2000)
  DECLARE @ActivityName varchar (2000)

  Select @ActivityID=inserted.ActivityID,@ActivityName=inserted.ActivityName 
    From inserted


  DECLARE @CustomerNo varchar(2000)
  DECLARE @CustomerName varchar(2000)

  Select @CustomerNo = B.[CustomerNumber]
        ,@CustomerName= B.[CustomerName]
    from [dbo].[Activity] A 
    inner join [dbo].[Customer] B 
      on A.[CustomerID]=B.[CustomerID]

  DECLARE @email VARCHAR(2000)

  SELECT  @email = RTRIM(U.[Email]) + ';'
    FROM [dbo].[Activity] A
      left join [dbo].[UserActivity] UA   
        inner join [dbo].[User] U 
          on UA.[UserID]=U.[UserID]
        on A.[ActivityID]=UA.[ActivityID]
    WHERE U.[Email]<> ''

  DECLARE @content varchar (2000)
    = 'ActivityID:' + @ActivityId + ' ' 
    + ',ActivityName:' + @ActivityName + ' ' 
    + 'has been created for' + 'CustomerNumber: ' + @CustomerNo
    + ' ' + '&CustomerName: ' + @CustomerName

  EXEC msdb.dbo.sp_send_dbmail
       @profile_name = 'LEADNOTIFY'
      ,@recipients = @email
      ,@subject = 'New Lead Found'
      ,@body = @content
      ,@importance ='HIGH'

END

The problem is in my code that I can't fetch the customer data and email from the respective tables properly.

My inserted table is the Activity Table. But i have to fetch the customer data from Customer table and fetch the email address from User Table by UserActivity (Which both have ActivityID as foreign Keys). I am having difficulties regarding these. — Nesma Huda, Jun 15 '20 at 06:51
To expand on Dale K's comment, a trigger fires once per _statement_, not _row_. If an `insert` statement adds 10 rows then the trigger fires once and `inserted` contains 10 rows. That said, `inserted` also contains `CustomerId` and `UserId` which you can use to `inner join` with the other tables. Aside: Triggers are generally expected to add minimal overhead. Sending email is better done elsewhere, e.g. have the trigger enqueue data for messages to be sent and have a separate task process the queue and send the emails. — HABO, Jun 15 '20 at 14:12

Dale K · Accepted Answer · 2020-06-15T23:16:20.753

I have written some code below which will loop through all the effected rows and send an email for each.

However before you read that, I would highly recommend (as @HABO commented) on using a different approach. Triggers are fine for some tasks, but 2 key things you want to keep in mind when using triggers:

Ensure its obvious to anyone developing the system that there are triggers - there is nothing worse as a developer than finding stuff magically happening on what seems like a simple CRUD operation.
Do whatever you do in your trigger as fast as possible because you are holding locks which not only affect the current session, but could easily affect other users as well. Ideally therefore you want to be performing set-based operations, not RBAR (Row By Agonising Row) operations.

Sending emails is a terrible thing to do inside a trigger because its not uncommon to be forced to wait for an SMTP server to respond. If you wish to trigger emails, a better way is to use the trigger to insert the email data into a queuing table and then have a service elsewhere which de-queues these emails and sends them.

All that aside the following code shows one way to handle the Inserted pseudo-table when you want to perform RBAR operations. Because in SQL Server the Inserted pseudo-table (and the Deleted pseudo-table) will contain the number of rows effected by the operation i.e. 0-N. Also I've hopefully joined your tables to correctly obtain the required information.

CREATE TRIGGER [dbo].[Activity_Insert_Mail_Notification]
  ON [dbo].[Activity]
AFTER INSERT
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @ActivityID VARCHAR(2000), @ActivityName VARCHAR(2000), @CustomerNo VARCHAR(2000), @CustomerName VARCHAR(2000), @Email VARCHAR(2000), @Content VARCHAR(2000);

  -- Get all the relevant information into a temp table
  SELECT ActivityID, ActivityName, C.CustomerNumber, C.CustomerName, RTRIM(U.[Email]) + ';' Email, CONVERT(BIT, 0) Handled
  INTO #ActivityTriggerTemp
  FROM Inserted I
  INNER JOIN Customer C on C.CustomerID = I.CustomerID
  INNER JOIN UserActivity UA on UA.ActivityID = I.ActivityID
  INNER JOIN [USER] U on U.UserID = UA.UserID;

  -- Loop through the temp table sending an email for each row, then setting the row as 'handled' to avoid sending it again.
  WHILE EXISTS (SELECT 1 FROM #ActivityTriggerTemp WHERE Handled = 0) BEGIN
    SELECT TOP 1 @ActivityID = ActivityID, @ActivityName = ActivityName, @CustomerNumber = CustomerNumber, @CustomerName = CustomerName, @Email = Email
    FROM #ActivityTriggerTemp
    WHERE Handled = 0;

    -- Build the body of the email
    set @Content = 'ActivityID:' + @ActivityId + ' ' 
      + ',ActivityName:' + @ActivityName + ' ' 
      + 'has been created for' + 'CustomerNumber: ' + @CustomerNo
      + ' ' + '&CustomerName: ' + @CustomerName;

    -- Send the email
    EXEC msdb.dbo.sp_send_dbmail
      @profile_name = 'LEADNOTIFY'
      , @recipients = @Email
      , @subject = 'New Lead Found'
      , @body = @Content
      , @importance ='HIGH';

    UPDATE #ActivityTriggerTemp SET
      Handled = 1
    WHERE ActivityID = @ActivityID AND ActivityName = @ActivityName AND CustomerNumber = @CustomerNumber AND CustomerName = @CustomerName AND Email = @Email;
  END;
END

Thank you very much for the solution @Dale K. I have used this method and got the required result. I have also written a code to use the trigger to insert the email data into a queuing table and then have a service elsewhere which de-queues these emails and sends them. — Nesma Huda, Jun 19 '20 at 15:00

Email data from several tables which is joined with inserted table

1 Answers1