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

[agneum]

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".

[Lisabel0]

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

[agneum]

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:

 

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]

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]

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]

This post of mine might help:

https://baizini-it.com/blog/index.php/2018/05/10/powerapps-concurrently-returning-variables-and-mult...

[agneum]

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]

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.