none
SQL On Insert Table Trigger Doesn't Run When Record Added Through SSIS RRS feed

  • Question

  • When a record in inserted into a specific table, I need database mail to get send out notifying that there's a new record in there. I setup a table trigger for on insert. This was tested successfully when just manually inserting a record in SQL Management Studio. Database mail gets sent out as expected. However, I have an SSIS package that runs on a schedule and it's doing the inserting. However, when a new record gets added to the table with the on insert trigger, no mail is sent out. I don't see a record of it at all in sysmail_allitems. Any idea why this would happen?

    My trigger: 

    ALTER TRIGGER [stg].[AlertNewRecord]
    ON [stg].[ActiveList] 
    AFTER INSERT
    AS 
       EXEC msdb.dbo.sp_send_dbmail 
                            @profile_name = 'SMTPProfile', 
                            @recipients = 'myuser@mycompany.com' , 
                            @body = 'A new record is in this table.', 
                            @subject = 'New Record Created' 

    Monday, May 20, 2019 7:46 PM

Answers

  • Hi hellomynameiskayla,

    Could you please tell me the task that you use to do the inserting in SSIS Package? Data Flow Task or Execute SQL Task?

    If you use Data Flow Task with OLEDB Destination, it's better for you to specify FIRE_TRIGGERS as part of the OLE DB component through its Advanced Editor.

    Please refer to Why arent my triggers firing during an insert by ssis .

    Best Regards,

    Mona Lv


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, May 21, 2019 5:56 AM