cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Advocate II
Advocate II

dml triggers causing failure of table updates

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

17 REPLIES 17
Community Support
Community Support

Hi gcoates,

 

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.

Regards

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

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! Smiley Happy 

 

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.

 

Thanks

gcoates

Hi gcoates,

 

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:

UPDATE with OUTPUT clause – Triggers – and SQLMoreResults

Also a thread for reference:

Cannot use UPDATE with OUTPUT clause when a trigger is on the table

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.

Regards

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

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.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Top Solution Authors
Top Kudoed Authors
Users online (6,090)