1

I am trying to create an Azure Logic App workflow with SQL Trigger. The trigger that I am using is "When a row is inserted". However, the workflow is not getting triggered even after the records are inserted successfully in the table. I have followed all the pre-requisites as mentioned on the official MS documentation but it still doesn't work. Below are the details about my implementation.

  • Details of Logic App

Azure Logic App Type: Logic App Standard

Workflow Type: Stateful

Trigger: When a row is inserted (Not using V2)

Trigger Type: In App (built-in) since my logic app is deployed in an ASE v3

Database: SQL Server

  • Details of Database

Database used: SQL Server

Azure SQL / On-Premise DB: Its an On Premise SQL Server DB. The network where DB is installed is peered with the Azure Virtual Network where the Logic App is deployed and the DB is accessible.

Is able to connect to DB using Azure Logic App Work flow: Yes, I created a separate test workflow using HTTP trigger and added a SQL Action "Insert Row" to check if I am able to access the DB from the logic app and if I am able insert a record in the DB Table. This worked totally fine. The record was inserted when I sent a request to this HTTP triggered test workflow and later I added another action "Execute Query" with a simple SQL query

    Select * From Test;

to check if it fetches the records and it successfully fetched the record that was inserted in the previous step.

Is Change Tracking Enabled on the DB: Yes

Is Change Tracking Enabled on the Table: Yes

Does table has an Identity Column: Yes, it has a Integer Identity Column (1,1)

Does table has a RowVersion Column: Yes

I tried to use the other trigger "When a row is modified" as well but that too is not getting triggered.

Even after all this setup, its not working. Please let me know if I am missing anything. Any help is highly appreciated.

Thanks and Regards,

Amit Anand

Improve this question
1
  • did you have any luck with this? I feel like I've done everything including the answer below and it still isn't working Commented Jun 19 at 9:29

2 Answers 2

Reset to default
0

To trigger the logic apps for **Azure SQL/On-Prem SQL ** using SQL triggers, you need to enable CHANGE_TRACKING on both level Databse and Table . If CHANGE_TRACKING is not enabled, even if any changes is made, it won't trigger the SQL Triggers.

Note :- change tracking can be enabled on tables which has Primary Key/Any other key.

For reference check these MS Documents.

This worked for me.

I have used Azure SQL here. I have created table logic_app and enable tracking on both level.

//DATABSE LEVEL
ALTER DATABASE lasqldb
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 2  DAYS, AUTO_CLEANUP = ON)

//TABLE LEVEL
ALTER TABLE logic_app
ENABLE CHANGE_TRACKING

OUTPUT:

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

I faced the same problem and I'm not sure if it'll fix it for others but it does also need the following db roles

CREATE USER [logicapp-mi-name] FROM EXTERNAL PROVIDER;
ALTER ROLE db_owner ADD MEMBER [logicapp-mi-name];
ALTER ROLE db_datareader ADD MEMBER [logicapp-mi-name];
ALTER ROLE db_datawriter ADD MEMBER [logicapp-mi-name];

I think it needs db_owner because it does add a bunch of things initially to the sql database. I'm actually not completely sure whether it needs db_owner or something more restrictive would work because it is not mentioned anywhere in the documentation.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.