12-07-2016 11:02 AM
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
12-08-2016 12:10 AM
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.
12-08-2016 12:37 AM
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.
12-08-2016 01:58 AM
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
12-08-2016 02:08 AM
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!
12-12-2016 03:56 AM
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.
12-13-2016 11:17 PM
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.
02-02-2017 06:46 AM
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.
02-20-2017 03:53 AM
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?