Trigger in ADF

Question

I am working on an ADF pipeline that pulls data from an external SQL database. Instead of running the pipeline on a fixed schedule, I want it to be triggered automatically whenever new or updated data is available in a specific table within the SQL database.

How do I schedule a pipeline to trigger automatically based on data availability in an external SQL database? What is the best approach in ADF to detect the availability of new or updated records in the SQL database?

Answer 1

Hi @DataCoder
Thank you for the question and using Microsoft Q&A platform.

To trigger an ADF pipeline automatically whenever new or updated data is available in a specific table within the SQL database, you can use the "Event-based trigger" feature in ADF.

Here are the high-level steps to set up an event-based trigger in ADF:

1. Create a new trigger in your ADF pipeline and select "New/Edit" under "Trigger type".
2. Select "Event-based trigger" as the trigger type.
3. Choose the SQL Server you want to monitor for changes.
4. Select the table you want to monitor for changes.
5. Choose the type of change you want to monitor for (insert, update, or delete).
6. Set up the conditions for the trigger to fire. For example, you can set a threshold for the number of rows that need to be inserted or updated before the trigger fires.
7. Save the trigger.

Once the trigger is set up, it will monitor the specified table in the SQL database for changes. When the conditions for the trigger are met, it will automatically trigger the ADF pipeline to run.

To detect the availability of new or updated records in the SQL database, you can use the "Lookup" activity in ADF. The "Lookup" activity can be used to query the SQL database and check for new or updated records. You can then use the output of the "Lookup" activity to determine whether the trigger should fire.

https://learn.microsoft.com/en-us/azure/data-factory/how-to-create-custom-event-trigger
https://learn.microsoft.com/en-us/azure/data-factory/how-to-create-event-trigger?tabs=data-factory

Hope this helps. Do let us know if you any further queries.

---

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Answer 2

Assuming it is an Azure SQL database, you can leverage External REST APi method to trigger the pipeline on event based:

https://datasharkx.wordpress.com/2022/12/02/event-trigger-azure-data-factory-synapse-pipeline-via-azure-sql-database/

In case if is a SQL server, you can create a Powershell job at server level and create a database trigger to trigger that SQL job on insert/update or delete