cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
agneum
Level 8

EditForm doesn't edit sql server gallery with instead of trigger.

I can't believe my previous post was marked as spam. This thread migt be the reason some people are getting "an error occured on the server" and similar errors such as "There was a problem saving your changes. The data source may be invalid" when trying to edit values in their editform!

 

Steps to reproduce:

Create a gallery sourcing an sql server database where Items=[dbo].[yourtablename], and an editform field in edit mode in the same screen. Make buttons that can select posts to edit, and a confirm button that does SubmitForm.


Try editing a post, everything works fine, right? 

Now, try adding an instead of trigger in SSMS for that table for both insert and update that do the following:


1) Declare variables with same definitions as in your table

2) Select into declared variables from the Inserted table
3) Finally insert the variables into the table.

For example:

CREATE TRIGGER [dbo].[t_varianceControl]
ON [dbo].[production]
INSTEAD OF INSERT, UPDATE
AS
BEGIN

 

-- Obviously the definitions and number of variables should match table definition

DECLARE
@dateKey int,
@customerId varchar(20),
@article varchar(20),
@qty int,

-- Stores vars from the attempted insert
SELECT
@dateKey = I.dateKey,
@customerId = I.customerId,
@article = I.article,
@qty = I.qty
FROM
Inserted I

-- Insert, productionId will be generated as it is an identity column.

INSERT INTO
dbo.production(dateKey,CustomerId,article,qty) 
VALUES
(
@dateKey,
@customerId,
@article,
@qty
)

END
GO


Go back to powerapps and try editing a few posts. Doesn't work!

Is there a good explanation as to why?


I always thought that an edit in Powerapps would equal an update query.. something like "Update this table where primarykey = selectedvalue".

7 REPLIES 7
Lisabel0
Level 8

Re: EditForm doesn't edit sql server gallery with instead of trigger.

Sorry but some descroptions are not quite clear, could you add some pictures of your app to your explanation maybe?

agneum
Level 8

Re: EditForm doesn't edit sql server gallery with instead of trigger.

I have a very simple app with a gallery that connect to an Azure SQL Table. On that table I have an INSTEAD OF UPDATE trigger. When trying to create an Edit form with powerapps that updates a post I get the errors above.

 

However, https://docs.microsoft.com/en-us/connectors/sql/ explains that it is not possible to include triggers or run stored procedures. 

So, this is just not possible it seems. Is there any way to work around the limitations? I really, really don't want to complicate things by using flows, JSON, gathering data in a Collection, just so that I can some simple logic on the server.

 

In Excel, you can read from basically any table, view or even function defined on the server and specify the parameters for it as well. See the following image below:

 mtvf.PNG



I figure Powerapps should work much the same, simply returning the dataset in a Collection much like Excel just shows you the data. Excel doesn't require you to define columns, it just does what it is told. I really wish I could have a window like above in Powerapps, it would make things so much easier. 


Meneghino
Level 10

Re: EditForm doesn't edit sql server gallery with instead of trigger.

You can read any table or view in PowerApps directly, whether it has a trigger or not.

What you cannot do is write to a table with a trigger or any view, but you can't do that in Excel either.

One way to write to a table with a trigger is to invoke a stored procedure from PowerApps via Flow.  This is actually easier than it sounds.

agneum
Level 8

Re: EditForm doesn't edit sql server gallery with instead of trigger.

Okay, I trust you and will attempt to invoke a stored procedure via Flow. It just seems that it would be messy since I would have several of them. 

 

Can I call SQL Multi-Statement Valued Functions, and pass paramaters? Guessing this should be possible. The reason is not necesarrily to change them, but to read the data which is why I brought up the Excel example.

I'm just trying to return data from SQL, and have mayve a few procedures for editing the data. The support for Views in powerapps still doesn't allow that as far as I know.

 

Thanks for the quick reply!

Meneghino
Level 10

Re: EditForm doesn't edit sql server gallery with instead of trigger.

agneum
Level 8

Re: EditForm doesn't edit sql server gallery with instead of trigger.

Thanks for the article. As you say, it doesn't look "difficult" per se, but having to include the JSON, and a whole flow for just one function seems a bit excessive. But I'll wait to pass judgement until I've tried it out.

Meneghino
Level 10

Re: EditForm doesn't edit sql server gallery with instead of trigger.

If the shape of the data is the same between your various functions, then you could maybe have just one flow and add a parameter to identify the function to call.

In any case, I agree that it would be much better to have the possibility of adding a SQL Server TVF as a data source in PowerApps directly.

Helpful resources

Announcements
Better Together’ Contest Finalists Announced!

'Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Join THE global Microsoft Power Platform event series

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

thirdimage

Microsoft Business Applications Virtual Launch

Join us for the Microsoft Business Applications Virtual Launch Event on Thursday, April 2, 2020, at 8:00 AM PST.

thirdimage

Community Summit North America

Innovate, Collaborate, Grow - The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors
Users online (5,566)