By the time of writing this article, the Logic App Standard SQL Connector does not have the functionality to monitor the row version of SQL Views so it can't be triggered by a change in the View's data, which would have allowed us to configure a trigger on a View in SQL. Until it gets rolled out, we are exploring a way in this article to imitate this functionality.
The SQL built-in trigger (SQL Server - Connectors | Microsoft Learn) is based upon tracking update on SQL table and tracking cannot be enabled for SQL views. Azure SQL trigger uses SQL change tracking functionality to monitor a SQL table for changes and trigger a function when a row is created, updated, or deleted.
Assuming that we have a SQL Server, with three tables, and a View that joins the three tables. If any of the tables has an update, it will reflect on this View. This is what we tested in this POC, you can change this based on your requirements and based on how your View gets updated, if it gets updated only by two tables and the third is just static data then you will only need two Parent workflows to trigger the child one. The idea here is to pass the triggered value and use it as a where condition in the child workflow. The child workflow will execute a Get rows action on the SQL View using the "where condition", it will then do the select on the View instead of a table as we use the View name instead of a Table name.
SQL side:
To begin, you might need to whitelist your client IP if you are connecting to your SQL Server from your machine.
If that doesn't work, you can whitelist your IP from the Networking section under the SQL Server (browse to the SQL Server from the Database Overview page then go to Networking).
We create the tables in SQL Server.
We create the SQL View.
We enable Change Tracking on the Database and on the Tables. (right click, properties), you can also do this using code as well.
Create the Logic App and Workflows:
We create a Logic App Standard.
We create four workflows. (for the triggering workflows, you can have one only or more, based on your requirements)
tst_Workflow_SQL_Trigger_Tbl1
tst_Workflow_SQL_Trigger_Tbl2
tst_Workflow_SQL_Trigger_Tbl3
tst_Workflow_SQL_Get_View_Updated
Design for Child workflow that will get the updated rows of the SQL View:
Add a Request trigger.
Add the below schema to the Request Body so we can easily pass the values when calling this workflow from the Parent workflows.
Add an action to Get Rows for a table. Select the built-in SQL Connector and select the Get Rows Action.
In the Table Name click on Custom value and enter the name of the SQL View manually.
In the Parameters, add Where condition.
Select the Outputs of the Request Trigger to populate the where condition (will translate to: id=value)
Add a Response Action so the Child workflow can respond back to the Parent workflow.
Here you can precede this Action with a condition to check the output of the Get Rows Action and respond accordingly.
You can respond with the Output of the Get rows Action, but to steer away from repeating the work in the Parent workflows it is better to do all the work in the Child workflow. So you can act upon the result of the triggered SQL View in the Child workflow.
Design for the triggering workflow for Table 1 (Parent workflow):
Add a trigger. Select from the built-in tab the SQL Connector, select the Trigger When a row is modified (note the difference between this trigger and the When a row is updated, select the one that matches your requirements, even When a row is inserted)
After creating the connection, select the Table that you want to trigger this workflow. Table 1 in our scenario.
Add a Parse JSON Action. Use a sample of the table single row data to create the schema.
Sample:
{
"Id": 20004,
"Auhthor_id": 7346,
"Price": 57,
"Edition": 6,
"RowVer": "AAAAAAAAtA8="
}
Finally for this workflow, add an Action to call another workflow, the child workflow.
As we have created the child workflow earlier, the parameters for the workflow should be accessible.
For the Id, use the name that is used in the View, so you can easily select that exact column.
For the value, pass the value from the parsed JSON for that column. In our case it is called Id.
Create the other two workflows in the same manner. Point the trigger for each workflow to the correct Table.
In the Parse JSON use the schema for the relevant table.
In the Invoke Action, use the correct name of the column, and select the correct value from the Parse JSON output parameters.
Testing:
Add or update a row for one of the tables in SQL, you will notice that the corresponding Parent workflow was triggered, and called the Child workflow.
The Child workflow would get the updated row in the SQL View based on the passed where condition.
You can alter the where condition and the passed parameter based on your requirements.
This article is only a prove of concept.
Thank you :)