none
sp_send_dbmail trigger on multiple row insert RRS feed

  • Question

  • Hi,

     

    I have a SQL Agent Job that selects records that are of a particular age (from Table1) and inserts them into another table (Table 2) - multiple records get inserted as a single INSERT step. On Table 2 I then have a trigger set FOR INSERT which I was hoping would send an email using the fields copied from the batch job, one of which being an email address.

     

    The trigger is: -

     

    Code Snippet

    CREATE TRIGGER trgSendMail ON Table2 FOR INSERT AS

    DECLARE @myEmail VarChar(50)

    SELECT  @myEmail = strEmail FROM Inserted

    EXEC sp_send_dbmail
     @Profile_Name = '{MailProfile}',
     @Recipients = @myEmail,
     @Subject = '{MailTitle}',
     @Body  = '{MailBody}'

     

     

     

    Thing is, the agent works fine but the trigger only sends an email to the first row inserted.

    Any ideas on how to get around this so that if the batch job inserts 10 rows into Table2 then 10 emails are sent out?

     

    Shaun.

     

    Friday, February 15, 2008 4:21 PM

Answers

  • Your TRIGGER is collecting only the first rows data and placing it into the variables.

     

    Such a 'non-standard' use of TRIGGERS will never scale, and should NOT be usable as expected with multi-row actions.

     

    The 'good' option would be to have the TRIGGER place the rows into a MailToBeSent table, and then have a SQL Agent Job fire every few minutes and send mail to the data, and then remove the rows (or set a MailSent flag.)

     

    A 'better' option would be to explore using the Service Broker queueing functionality. It will prove to be more robust and managable.

     

     

     

    Friday, February 15, 2008 4:59 PM
    Moderator

All replies

  • Your TRIGGER is collecting only the first rows data and placing it into the variables.

     

    Such a 'non-standard' use of TRIGGERS will never scale, and should NOT be usable as expected with multi-row actions.

     

    The 'good' option would be to have the TRIGGER place the rows into a MailToBeSent table, and then have a SQL Agent Job fire every few minutes and send mail to the data, and then remove the rows (or set a MailSent flag.)

     

    A 'better' option would be to explore using the Service Broker queueing functionality. It will prove to be more robust and managable.

     

     

     

    Friday, February 15, 2008 4:59 PM
    Moderator
  •  

    Here it is, (without using cursor)

    Code Snippet

    CREATE TRIGGER trgSendMail ON Table2 FOR INSERT AS

     

    DECLARE @myEmail VarChar(50)

     

    Declare @InsertedData

    Table

    (

                    rowid int identity(1,1) ,

                    strEmail  varchar(50)

    );

     

    Insert INto @InsertedData

                    SELECT strEmail FROM Inserted

     

    Declare @i as Int;

    Select @i= max(rowid) from @InsertedData

     

    While @i>0

    Begin

     

                    Select @myEmail = strEmail from @InsertedData where rowid = @i

                   

                    EXEC sp_send_dbmail

                     @Profile_Name = '{MailProfile}',

                     @Recipients = @myEmail,

                     @Subject = '{MailTitle}',

                     @Body  = '{MailBody}'

     

                    Set @i = @i -1

    End

     

     

    Friday, February 15, 2008 5:01 PM
  • Jeepers, Mani,

     

    I would NEVER loop to an out of process thread from inside a TRIGGER.

    (I wouldn't do it even one time!)

     

    That is just asking for trouble. And a debugging nightmare!

     

    This is what Service Broker is best used to accomplish.

    Friday, February 15, 2008 5:10 PM
    Moderator
  • Yep. Agree with Arnie...

     

    Friday, February 15, 2008 5:15 PM
  • Not exactly what I wanted to read but I guess its best to do things properly.

    Thanks.

    Monday, February 18, 2008 11:55 AM