Thursday, August 02, 2018

Azure Logic Apps–Sql Server Triggers being skipped

I was trying to use the Logic Apps “Sql Server” trigger (When an item is created), and the trigger was always being skipped.

After much testing and looking, I found the following requirements for a SQL Server trigger: https://flow.microsoft.com/en-us/blog/introducing-triggers-in-the-sql-connector/

Limitations

The triggers do have the following limitations:

  • 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 table with the following definition will trigger LogicApps, when data is added (or updated, based on the type of trigger you use):

CREATE TABLE [dbo].[Test2](
     [Id] [int] IDENTITY(1,1) NOT NULL,
     [RowVersion] [timestamp] NOT NULL,
     [Message1] [nvarchar](50) NOT NULL,
     [Message2] [nvarchar](50) NOT NULL,
  CONSTRAINT [PK_Test2] PRIMARY KEY CLUSTERED
(
     [Id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Its unfortunate that LogicApps works without any errors, when you dont have an identity and a rowversion column defined on your table. It would have saved me some time if it had!

1 comment:

Pooja Garg said...

Thank you for sharing. its helpful for me.