Introducing triggers in the SQL Connector

The SQL Database Connector in Logic Apps and Flow is one of the most popular connectors, and we have just added a highly requested feature – triggers.  Now, you can kick off your Flow or Logic Apps whenever a row is inserted or modified in a SQL Azure Database table.

Using the trigger is straightforward.  Select the appropriate trigger.  Create your connection (if you have not already) or select an existing connection.  Then, select the table from the drop-down. [If you don’t see your table, see the notes below.]  You can also choose to further limit the rows returned by specifying a filter.

Once you configure the trigger, you can now use the output from the trigger in any action in your Flow or Logic App.  The trigger will make available the columns of the selected tables.

You can now save the flow, and it will kick off whenever a row is added to (or modified in) the selected SQL Database table.

That’s it.  You now have a working flow that you can use to monitor and automate whenever rows are added or modified in your SQL table.

 

Limitations

The triggers do have the following limitations:

• It does not work for on-premises SQL Server
• Table must have an IDENTITY column for the new row trigger
• Table must have a ROWVERSION (a.k.a. TIMESTAMP) column for the modified row trigger

 

A Brief note on the design and on the limitations

Some of you might have noticed that this feature has been available for some time now as a limited preview feature of Logic Apps in the East US 2 region.  Designing and implementing a trigger is more complex than adding actions.  This is because, the trigger needs to monitor and track changes.  In the case of SQL Databases, unfortunately, there is no mechanism of tracking changes that will work for all tables.  Therefore, specific tables must have specific column types which are designed for change tracking.  In order to track changes like addition or modification of rows in a table, the table must have a column whose value is unique and whose value increases (or decrease) monotonically each time such a change is made.  This is satisfied by having an IDENTITY column for tracking creation, and ROWVERSION (a.k.a. TIMESTAMP) column for tracking modification .

So, what happens when there is no such column in a table?  Those tables will not be listed when you try to use the trigger.  And, it will not work if you do try to type the table name manually.  The only workaround then is to externalize the state yourself and use the “Get rows” action to query for the changes.

We would, of course, like to hear your feedback.