"When an item is created" trigger (SQL in Azure) not working

[Anonymous]

Hi,

I've created a flow that should trigger when an item is created in a Azure SQL Database.
The flow already worked before, 2 days ago it stoped working - no errors, it simply won't trigger.
I've aready checked/done the following:

• The database connector is ok (status is "connected"). It works on other flows.
• When I insert the trigger, the flow correctly picks up the database and table I need
• The table itself has an "Identity" column and "Rowversion" column
• I've even tried to create a new connector to the same database, but I get the same result (flow won't fire)
• I also tried to create a new flow with the same trigger, but it wont start either
• When I test the flow, after it says "To see it work now, perform the starting action. This may take a few moments". I insert a row in said table and after some minutes the message changes to "Flow run timed out because the starting action was not performed. Please try again."
• The flow shows no runs after I insert a row in the table

What am I missing here? Any suggestions about how should I troubleshoot further?

The "dummy" flow that also doesn't works is as simple as this:

Any help is appreciated. 

Thanks
Best regards

[v-alzhan-msft]

Hi @Anonymous ,

The trigger connector that you using in flow is SQL Server connector not Azure DB.

Could you please take a try to create a flow wirth "Insert row (V2)" of SQL Server connector to insert a row to the table and check if the flow with trigger "When an item is created" of SQL Server connector would run?

Best regards,

Alice       

Community Support Team _ Alice Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi,

I've created a flow that inserts a row on that table. The flow ran sucessfully and in the first run it did fire the other flow with the trigger. I've done a few runs after (with different data, to insert different rows) and they did not work.

"Insert row (V2) flow:

Runs:

"When an item is created" flow runs:

Best regards

[Anonymous · Accepted solution]

Update for whoever finds this topic and has the same problem:

I've contacted MS Support and after further testing, long story short, the issue has been escalated to the product team. I'll post another update asap.

Just to clarify the problem we found during testing: we create an "When an item is created (V2)" trigger which will only trigger once. It won't trigger again later. In my case the trigger checks for new data every minute and it never finds new data in the database even if new rows were inserted in said database.

Final update:
The product team informed me the following:
"The way trigger works is we keep polling sql with the largest value in the ID column we have seen. Whenever sql gives us a bigger number the trigger fires and we store the new number."

Indeed, I was using a table to test the flow so I kept truncating the table before a new run.

So, the trigger kept the last ID there was in the table (lets say, 6) and I truncate the table. Next time I ran the test flow a new row was created with ID 1, so the trigger wouldn't fire. I never ran the test flow enough times to have an ID bigger than 6 so the trigger never fired again. After that reply from the Product Team, I ran the flow enough times to increase the ID after 6 and the trigger worked after that.

[Anonymous]

Thanks for this.
I am facing a similar problem where I had to truncate the table and flow isn't getting triggered.
Is there a way where we can actually delete the largest value stored by Power Automate so that next time, whenever the entry comes with ID 1, it gets triggered.

[Anonymous]

There is no way to do it, at least not that I know of.
If you really have to use that trigger and if you really have to use that table that keeps getting truncated, I'd suggest using another table where you should have an automatic ID as usual and another column with your "main" table new ID. Use a DB trigger so that whenever a new record is inserted in your truncated table (the "main" table) it should grab the new ID and insert in this scondary table.

Use the Power Automate trigger in that second table. Since you have an automatic ID the trigger will keep firing, and then you can read the "main" table ID from the second column - then you know which record was inserted in your truncated table.

[Anonymous]

Do you mean i have to add table itself has an "Identity" column and "Rowversion" column and that Identity column will anyway auto increase.My PK is a text column.Is it because of that , it is creating problem.

[Anonymous]

The PK beeing text should not be a problem. Check point 5 from Know Issues and Limitations (https://docs.microsoft.com/en-us/connectors/sql/#known-issues-and-limitations😞

When invoking triggers, we have the following limitations:

• A ROWVERSION column is required for OnUpdatedItems
• An IDENTITY column is required for OnNewItems

So, if you don't have an IDENTITY column, create one and it should work. That column will auto-increase by definition (check here: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql-identity-property?vi...)

---

[Benjamin_Adam]

I had this problem today. This thread was very helpful, thanks for that. I was able to fix the issue that the trigger doesn't work after a truncate. 

I just removed the trigger from the flow and added it directly back, then i just had to set triggerOutputs()  i the following steps.
After that the trigger has fired again to identity 1