Forum Discussion
Executing SQL trigger doesn't work if triggering events are generated by Azure Data Factory
Good day everyone,
I have an issue getting data from Azure Data Factory into SQL. My original solution of using power automate was stymied by the combination of a mysteriously slow Stored Procedure when running it from Power Automate vs SSMS and the hard coded 2 minute timeout in flows. So my next idea was to run the SP from a trigger whenever records are inserted into a staging table from data factory using Copy Data action.
*The trigger works fine when adding records in SSMS
*The Data Factory pipeline successfully inserts rows into the staging table
*After successful inserts from DF, the trigger in SQL on that staging table does not fire.
Is this a bug? How can I work around this? And finally, is there a better way that doesn't involve deploying more services/licenses?
Edit: To clarify what I' masking about. Key scenario I am attempting is in bold above. However, I have just decided to run the SP from Data Factory and pay the fees to run it. I don't have any more time to waste trying to troubleshoot.
- Your post is much to vague to give an answer on it.
But if you perform a BULK operation, then by default trigger don't fire, see
https://learn.microsoft.com/en-us/sql/t-sql/statements/bulk-insert-transact-sql?view=sql-server-ver16#fire_triggers- Does Data Factory use bulk inserts in the "Copy Data" activity? The inserts into the SQL staging table are done using "Copy Data" activity.
