The blog post describes an ingenious method to use the Logic App Standard with SQL Connector, even though at present it does not incorporate the function to monitor SQL View's row version for triggers. This lack leaves us no choice but to devise an effective workaround, which is the key concern discussed in the blog.
The SQL built-in trigger is typically applied to track updates on SQL tables. However, tracking cannot be enabled for SQL views, an aspect that Azure SQL trigger targets using SQL change tracking functionality to watch changes occurring in a SQL table.
The article assumes an available SQL Server with three tables and an SQL View that consolidates these tables. Any changes in these tables would reflect on the View. The blog illustrates this process through a Proof of Concept (POC) that can be adapted as per unique requirements and the specific schema of the View.
The core idea proposed by the author involves passing the triggered value from the parent workflows to the child workflow, which then executes a 'Get rows' action on the SQL View using the triggered value as a 'where' condition. The 'Get rows' action is specifically developed to select from the View rather than a table.
The blog also guides you through the setup process, including how to whitelist your IP if needed, how to enable Change Tracking, creating the Logic App, designing the Child workflow and the triggering workflow, as well as testing the whole setup.
With the absence of in-built functionality to monitor the row version of SQL Views in the Logic App Standard SQL Connector, the use-case described represents an innovative solution to circumvent this limitation.
The solution allows SQL views to be indirectly monitored for changes, through the integration with Azure SQL triggers. Configuring workflows to interact with specific SQL views, changes in the SQL tables are now capable of reflecting onto the SQL views, all viewed through a simple three-table and one-view system.
While the implementation of the solution in current Microsoft systems may seem intricate, the potential can be seen when adapted to suit specific requirements. Whitelisting connections, employing Change Tracking, and creating workflows are just stepping stones towards harnessing this capability fully.
This approach paves the way for robust database management by seamlessly integrating Microsoft Logic Apps with SQL Views, creating an interface that is flexible yet reliable.
The article discusses overcoming a current limitation in the Logic App Standard SQL Connector's functionality: its inability to monitor row versions of SQL Views, and trigger due to changes in views' data. The author provides a step-by-step explanation to simulate this functionality.
The SQL built-in trigger cannot be enabled for SQL views as it only watches updates on SQL tables. The Azure SQL trigger leverages the SQL change tracking functionality to monitor a SQL table for changes, thereby triggering a function upon creation, update, or deletion of a row.
To connect to your SQL Server from your machine, the article suggests you might need to whitelist your client IP. If that's unsuccessful, the IP can be whitelisted from the Networking section under SQL Server.
The instructions are then provided for creating the tables in the SQL Server.
Microsoft Logic App Standard, SQL Connector with Logic App, Azure SQL Trigger, Microsoft SQL change tracking, Logic App with SQL Server