Reply
Level: Powered On
Posts: 10
Registered: ‎12-07-2016

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

Community Support Team
Posts: 2,422
Registered: ‎09-20-2016

Re: dml triggers causing failure of table updates

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

Level: Powered On
Posts: 10
Registered: ‎12-07-2016

Re: dml triggers causing failure of table updates

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.

Highlighted
Level 10
Posts: 1,305
Registered: ‎11-07-2016

Re: dml triggers causing failure of table updates

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

Level: Powered On
Posts: 10
Registered: ‎12-07-2016

Re: dml triggers causing failure of table updates

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! 

 

Level: Powered On
Posts: 10
Registered: ‎12-07-2016

Re: dml triggers causing failure of table updates

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

Community Support Team
Posts: 2,422
Registered: ‎09-20-2016

Re: dml triggers causing failure of table updates

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

Level: Powered On
Posts: 2
Registered: ‎01-23-2017

Re: dml triggers causing failure of table updates

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.

Level: Powered On
Posts: 6
Registered: ‎01-19-2017

Re: dml triggers causing failure of table updates

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?

Level: Powered On
Posts: 6
Registered: ‎01-19-2017

Re: dml triggers causing failure of table updates

I would like comments of offical personals regarding the above mentioned issue. If there is any work around please let us know.