06-20-2019 18:06 PM
I've recenly been working with Flow's 'When an Item is modified' trigger for Azure SQL DB, and thought I'd share some learnings.
Pre-requisites, how it works, peformance suggestions and some considerations and pitfalls to avoid.
watch?v=K6wQqnEMsnI
Thanks, Solved.
Hi PaulD1,
THANKS for the explanation!!
The RowVersion in the SQL table was my problem that this trigger was NOT working.
dchan1
Not sure why you are not seeing the Index folder - maybe check you are on the latest SSMS?
To create an index on the RowVersion column you can run a statement like the one below (replace schema, table and RowVersionCol with the appropriate schema name, table name and name of your RowVersionColumn).
CREATE INDEX schema.idx_table_RowVersionCol
ON schema.table
(
RowVersionCol
)
GO
Yep, the whole row is returned - the check is just 'has the record changed' which is based simply on the RowVersion number - are there any RowVersion numbers higher than the last time the Flow checked.
If you want to know what has changed, you may want to look at making the table a 'temporal table' and creating a stored proc that, given the record ID, can compare the current with the prior record and tell you what has changed.
How do you get the data from only those columns that have been modified? It seem to return to whole row.
Great video. Just a question. I am using Azure SQL and I can't see Indexes folder just as you can in the video. When checking for the Top Queries I see that the it uses Clustered Index Scan on the whole row. I am not sure if this is okay or how to create an index just for the RowVersion column