All Content
Timespan
explore our new search
Logic App Standard & SQL Connector for SQL Views Triggering
Image Source: Shutterstock.com
Microsoft 365
Sep 11, 2023 6:18 PM

Logic App Standard & SQL Connector for SQL Views Triggering

by HubSite 365 about Microsoft

Software Development Redmond, Washington

Pro UserMicrosoft 365Learning Selection

Explore innovative methods to trigger SQL views using Logic App Standard and SQL Connector, despite current limitations.

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.

Diving Deeper Into The Topic of Logic App Standard with SQL Connector

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.

 

Read the full article Effective Use of Logic App Standard with SQL Connector to Trigger SQL Views

Learn about Effective Use of Logic App Standard with SQL Connector to Trigger SQL Views

 

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.

  • The proof of concept (POC) involves a SQL Server with three tables and a view that consolidates these tables. If there's an update in any of the tables, it's reflected on the view.
  • The functionality is then scaled based on requirements and how the View is updated. For example, if the View is updated only by two tables, only two parent workflows are required to trigger the child one.
  • The method passes the triggered value and uses it as a "where condition" in the child workflow. The child workflow executes a "get rows" action on the SQL View using the "where condition".
  • Then, the View name is used instead of the Table name to carry out a select operation on the View.

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.

 

More links on about Effective Use of Logic App Standard with SQL Connector to Trigger SQL Views

Trigger SQL Views with Logic App Standard with the built- ...
Apr 16, 2023 — 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 ...
Effective Use of Logic App Standard with SQL
Trigger SQL Views with Logic App Standard with the built-in SQL Connector is a feature of Azure Logic Apps that allows users to trigger a SQL view with a Logic ...
Is it possible to trigger during an SQL table insertion using ...
Jul 21, 2017 — The team has introduced triggers in the sql connectors that enable you to trigger your logic apps when there is a new record found.
Using an Azure Logic App to Query Data in an On- ...
Jul 23, 2020 — Create an Azure Logic App to connect to the SQL Server through the gateway ... Use HTTP Post and the URL from the “HTTP Request Received” trigger ...
Use Azure Logic Apps to Detect when a new SQL record ...
Oct 9, 2018 — ... Logic Apps and all their powerful connectors. In the portal, create a new Azure Logic App and then select Start with a blank template. Under ...
Logic Apps 101: Inserting Data Into Multiple Tables Using ...
What are SQL Triggers? Implementation. Creating SQL Tables, Stored Procedures and Triggers. SQL Tables; Stored Procedure and SQL Trigger.
Reliably receive SQL data in Logic Apps - Codit
May 19, 2017 — Receiving data from a SQL table and processing it towards other back-end systems; It's a very common use case in integration. Logic Apps has all ...

Keywords

Microsoft Logic App Standard, SQL Connector with Logic App, Azure SQL Trigger, Microsoft SQL change tracking, Logic App with SQL Server