“When an item is created” SQL trigger in Power Automate

There is a SQL Connector in Power Automate, and it has couple of triggers which are dependent on the IDENTITY/ROWVERSION columns in SQL:

The existence of those two columns in the table is a pre-requisite for the trigger to work:

https://docs.microsoft.com/en-us/connectors/sql/#when-an-item-is-created-(v2)

There is a catch there, though, and I probably would not know about it if not for a co-worker who shared it with me, and it’s that if we truncate a table, that may lead to some unexpected results.

Imagine that there is a table and there is a bunch of records there:

The query above will insert 600 records into the table, which means id_num will go up to 600:

Which will make the flow run 600 times:

So what if I truncated the table at this point? It would be empty after that:

This would reset indentity column seed, so the next time I added data to the table, the numbering would start from 1 again.

And the flow would not be triggered:

It won’t start for the next insert, and for the next one… not until I have 601 records in the table – that’s when it will kick in.

It seems the connector stores last identity column value somehow, and it won’t trigger the Flow till there is a record where identity column value exceeds the last known value.

Which makes sense except that, when in testing, where we tend to delete or truncate test data every now and then, this may lead to the Flow not running at all.

If you’ve managed to run into this, there are at least a couple of work arounds:

• Reset SQL identity seed to that new value exceeds what’s been cached by the connector: DBCC CHECKIDENT (WatchTable, RESEED, 700). Of course you’d need to know, at least approximately, what that value should be, but, unless you are concerned about gaps, that should not be a big problem. This is, probably, the fastest solution
• Another option would be to recreate the trigger. That’s easy to do in the dev environment – just add that Flow to a different solution, export as un-managed, delete the flow, the re-import the solution. In the downstream environments, where your solutions are managed, this might be more complicated. It’s still doable, but may first need to re-organize all your managed solutions to basically make it so that that particular Flow is in it’s own solution. In which case you should be able to delete that solution, then re-import, effectively re-creating the trigger along the way

Although, it might be easier to avoid it all together by not using “delete from…” instead of “truncate” on the SQL side. Since, well, “delete” won’t reset the identity seed.

This connector has some other limitations/caveats. It is definitely very useful when/if you need SQL integration in your flows, but you might want to be aware of those. Especially since they are all conveniently listed in the docs: https://docs.microsoft.com/en-us/connectors/sql/#when-an-item-is-created-(v2)

Have fun!