Hi, new to forum (and PowerApps!) but love the potential they offer. I'm currently getting an error when trying to submit a form from a powerapp to an access web app database table that has an "on insert" trigger configured. Tested the submit on a table without the trigger enabled and it works fine. Anyone else experienced this and can offer some advice or solution?Thanks
Could you please explain a bit for the " 'on insert' trigger " here? And what is the error message that you encountered when update the data?
Based on what I know, Access Web App uses Azure SQL database, and SQL server basically have two type of triggers:
Instead of trigger and the After trigger.
Please share some more information if you have the triggers configured at the data source side, I will try to test from my side, and share the results once finished.
Hi thanks for responding. The trigger is an On Insert event configured in the Access Web App table.
The error message I get when trying to submit the form and create a new record in this table is as follows:-
"The requested operation is invalid. Server Response: The target table [table name] of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause. inner exception: Microsoft SQL: the target table [table name] of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause."
I can't see any way to view/amend the submitform() function so cannot amend the SQL statement that is causing the error.
Thanks for your help with this - many of my tables in Access Web Apps have On Insert or On Update events configured so this is seriously limiting my scope currently with PowerApps. I hope it can be resolved.
Hi, I have exactly the same problem. In Access Web Apps I cannot patch to a table if it has an On Update trigger.
Removing the trigger allows for patching to occur.
Thanks @gcoates for discovering this, otherwise I would have spent a long time figuring out why my patch operation was not working
It would be great if this could be fixed.
There is however a workaround, although quite tedious: you can use Flow to trigger stored procedures (macros) in Access Web Apps so that you can:
1) In Access Web Apps create a data macro with the required operations (and with parameters to accept the ID of the changed/new/deleted record)
2) In Flow create the necessary operation to call the stored procedure with the required parameters
3) In Power Apps call the Flow operation
PS This does not work through a gateway I believe, so that you need SharePoint online
Glad to know I'm not alone!
Thanks for the workaround - my problem with that though is we are using the Access Web App through Sharepoint already so I really need to keep the On Insert routines attached to the tables. I don't even know if I could invoke the Flow from the Access Web App in order that I could remove the On Insert procedure.
Feels like it would be better fixed at source for the PowerApps submitform() function to me but as I said I'm a newbie so looking (hoping) for the simplest solution!
Hi @v-micsh-msft any update on this please? Not being able to use On Insert events in access web apps is a real limitation for my Powerapps - please let me know if you have been able to recreate the issue and if any fix is close.
Apologize for the late response.
Yes I could reproduce the error message you posted, on a SQL server. For the error message, there is a blog for reference:
Also a thread for reference:
I am trying to involve the others to take a further investigation on this issue.
There might be some time delay.
Thanks for your understanding and patience.
Same issue here. I have an on-premises SQL server, so I was able to run SQL Profiler and see the UPDATE statement that PowerApps generates -- it does have an OUTPUT clause without INTO clause.
I am facing same issue. I am using After Insert Trigger. I have also checked in profiles powerapp sents query with output without into clause. Is there any work around? any update?
I would like comments of offical personals regarding the above mentioned issue. If there is any work around please let us know.
Check out new user group experience and if you are a leader please create your group
Check out how to claim yours today!
We are excited to announce that Demo Extravaganza for 2021 has started!
On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks