Logic Apps 101 - Inserting Data Into Multiple Tables Using SQL Connector And Trigger(SQL)

Introduction

In modern-day integration, there are many cases where the front end (web app/API) is hosted in the cloud and some or a whole part of the data store is on the cloud or on-premises. The integration code has to populate the data /fetch and perform the CRUD operations on the tables based upon the request. In such scenarios, Logic Apps is best suited to integrate the web app/API and the data store as Logic App provides an easy and visual way of integrating the systems in the form of a workflow.

What are Logic Apps?

Logic Apps are a piece of integration workflow hosted on Azure which is used to create scalable integrations among various systems.These are very easy to design and provide connectivity between various disparate systems using many out of the box connectors as well as with the facility to design custom connectors for specific purposes. This makes integration easier than ever as the design aspect of the earlier complex integrations are made easy with minimum steps required to get a workflow in place and get it running.

Scope

This article deals with how to insert similar data into multiple tables in a data store using the logic app. The logic app exposes an HTTPS endpoint which can be consumed by the front end web API/app and it can send the data to the Logic App. The endpoints accept JSON payload and insert the data received over the HTTPS call into multiple tables hosted on an on-premises SQL server. This article aims to discuss how to insert the data to SQL tables using a single call from the logic app (by making use of SQL After Insert triggers) as opposed to the conventional way of calling two different stored procedures/insert row actions. The approach discussed in this article reduces the number of activities that a Logic App needs to perform and thus saves the user from getting billed for multiple actions (as a number of actions are billed during a logic app run). This article assumes that the reader is a beginner with a basic knowledge of Microsoft Azure and Logic Apps and guides the user to try a hands-on approach to learn the concept.

What are SQL Triggers?

As per the MSDN Documentation at CREATE TRIGGER (Transact-SQL)

"A trigger is a special kind of stored procedure that automatically executes when an event occurs in the database server. DML triggers execute when a user tries to modify data through a data manipulation language (DML) event. DML events are INSERT, UPDATE, or DELETE statements on a table or view. These triggers fire when any valid event is fired, regardless of whether or not any table rows are affected."

Implementation

The Implementation can be divided into two parts.

1. Creating SQL Tables, Stored Procedures, and Triggers
2. Creating Logic App and Connecting Logic App to the SQL Store

Creating SQL Tables, Stored Procedures, and Triggers

SQL Tables

A simple Employee Master Table is created which stores the information about the employee like the Employee ID, Employee Name, Salary, Reporting Manager Employee ID etc. The query for creating the simple employee table is as below.

CREATE TABLE[dbo].[Employee_Master]
    (
        [Emp_ID] int Unique NOT NULL, [Emp_name] varchar(100) NULL, [Emp_Sal] decimal(10, 2) NULL, [Supervisor_ID] int Unique NOT NULL
    )

Another table which stores the Employee and Reporting Manager can be created using the sample query as mentioned below.

CREATE TABLE [dbo].[Employee_Manager_Mapping]
(
[Emp_ID] int Unique NOT NULL,
[Supervisor_ID] int NOT NULL
)

Stored Procedure and SQL Trigger

The stored procedure to insert the data into the Employee_Master table is as follows.

CREATE PROCEDURE usp_OnboardEmployee @employeeId int,
@employeeName varchar(100),
@salary decimal(10, 2),
@managerid int AS BEGIN INSERT INTO [EMPLOYEE_MASTER]
VALUES
  (
    @employeeId, @employeeName, @salary,
    @managerid
  ) END

The SQL trigger is an After Insert Trigger which is created on the Employee_Master table and inserts the Employee Id and the Supervisor Id in the Employee_Manager_Mapping Table. The trigger is as follows.

CREATE TRIGGER trgAfterEmployeeOnBoardSP ON [dbo].[Employee_MASTER] FOR INSERT AS declare @empid int;
declare @SupervisorId int;
select
  @empid = i.Emp_ID
from
  inserted i;
select
  @SupervisorId = i.Supervisor_ID
from
  inserted i;
INSERT INTO Employee_Manager_Mapping
VALUES
  (@empid, @SupervisorId);
GO

Creating a Logic App

Before the Logic App can be created, it is necessary to set up an on-premises data gateway to enable the communication between the Logic App on the cloud and the SQL data store on the premises. Refer to Connecting to on-premises data sources with Azure On-premises Data Gateway . Once finished following steps can be done to create the logic app.

1. Select the Logic App from the Azure Market Place
   
   
2. Select the details like subscription, Resource Group, Location and then hit the create blade on the blade. Refer to the below screenshot.
   
   
3. Select the HTTP Trigger template from the list of available templates as shown below.
   
   
4. In order to define the input message, use the Upload Sample Payload To Generate Schema (highlighted in yellow). Paste the following sample message which can be used to generate the input schema. After that click on Advanced Options and select the method as a POST.
   

    

   {
   "EmployeeId" : 123,
   "EmployeeName" : "Mandar Dharmadhikari",
   "Salary" : 10000.20,
   "SupervisorId" : 345
   }

   Refer to the following sample screenshot for the configuration of the HTTP Trigger:
   
   
   
   
5. Once the HTTP POST request is received, the On-Premises SQL datastore can be updated with the employee details. The action to execute the stored procedure from the list of SQL action is to be selected. Refer to the sample screenshot.
   
   
6. Create the connection to the On-Premise SQL server data store using the on-premise data gateway. Refer to the following sample screenshot:
   
   
7. Select the SQL stored procedure usp_onboardEmployee which was created earlier and use the fields that are received from the HTTP Post call. Refer to the sample screenshot below.
   
   
8. Save the Logic App. This will create the endpoint that can be copied from the HTTP trigger and used to test the solution developed.

Testing

A utility like POSTMAN or SOAP UI can be used to test the Logic App created above. The sample message used while creating the JSON payload for the HTTP trigger is used for testing. The payload is as follows.

{
"EmployeeId" : 123,
"EmployeeName" : "Mandar Dharmadhikari",
"Salary" : 10000.20,
"SupervisorId" : 345
}

The URL copied from the HTTP Trigger is the one which should be consumed by the POSTMAN. The content-type header for the request should be set to application/json. Following is the screenshot of the request that is sent to the Logic App.

The following screenshot confirms the successful execution of the Flow.

 

When the following query is run on the on-premises data store, the results are returned as shown in the screenshot following the query.

Select * from Employee_Master
Select * from Employee_Manager_Mapping

 

Conclusion

As is evident from the testing results, a single action from the logic app can be used in conjunction with the SQL trigger when similar data is to be updated across multiple SQL tables in the data store.

References

Content from the following articles was referenced while writing the article.

1. Connecting to on-premises data sources with Azure On-premises Data Gateway
2. CREATE TRIGGER (Transact-SQL)