Insert into SQL table with active triggers implemented

[Lee]

Hi guys,

I can successfuly get flow to insert a row into one of my SQL tables. Now the issue I have is that I need to set up a trigger on this table to get the same data sent pover to  a different table on a different Server.

Since implementing the trigger Flow will not insert into the original table:

error:

"status": 400,

  "message": "Invalid Mashup Expression using supplied values.\r\n     inner exception: Microsoft SQL: The target table 'dbo.TABLE' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause.",

  "source": "sqlconnectionprovider-westeurope.am2-ase-001.p.azurewebsites.net"

Is there any way around this please?

For the error I suspect that the SQL Flow generates in the back end to get the data into my table is the issue by not using the into clause when using the output clause?

many thanks

Lee

[v-yamao-msft]

Hi Lee,

Which trigger are you using? I assume that your flow is relies on SQL Server, but currently, we do have actions for SQL Server, but we don’t have a trigger for it.

From the error message, it seems that your flow doesn’t have any enabled triggers. Please show me a screenshot of your flow for a further investigation.

Best regards,
Mabel Mao

[Lee]

Hi Mabel,

in the FLow: once an approval email has been approved I use Flow to move the data into a SQL table lets call it table 1.

that is the end of the Flow. this was working fine with no issues, data successfully getting inserted into table 1.

Now completetly outside / seperate of Flow  I needed to get the data from table 1 and insert the new row over to a seperate sql server db table (call it table 2) so I created a trigger on table 1 to get the data over to table 2. working fine as it is a simple after insert trigger on table 1. this was working when tested.

Now when I returned to flow and started a new flow I was then getting the error from FLow. After implementing the trigger on table 1 Flow was then unable to insert any data into table 1.

I presume when Flow creates the SQL to insert into table 1 it is using an output clause and no into clause within the same script which then stops flow from being able to insert the data

many thanks for your help.

please ignore the first reply, had an error half way through typing it and deleted it but it got posted in the end

many thanks

Lee 

[SameerCh · Accepted solution]

Lee,

You are right!  This is a current limitation on the SQL connector that updating a row do not work when the SQL Table has a SQL Trigger defined on it.  While we have plans on fixing this, we don't have a timeline yet.

There is no workaround for this today w/o making changes anything on the SQL side.

The feasible workarounds you can do are:

(1) Use a stored procedure

(2) Remove the trigger from your SQL Table

Obviously, (1) is the preferred method.  Let know if that works.

Thanks!
Sameer

[Lee]

Hiya Sameer

yep thought so 🙂 pity

Already beat you to it,

Stored procedure armed and ready.

Flow now working as before.

cheers

Lee

[SmartMeter]

Hi @SameerCh, 

Is there any timeline on this as of 11/7/2017 ? I can certainly make a stored proc for this, but I would much rather leave my trigger in place, to propagate the updates on insertion, precisely when they are needed.

Many thanks, 

SmartMeter 

[dhcinfotech]

How soon are we getting this? Is there an update??? We NEED this.

[dhcinfotech]

How long before we get this? Any updates??? We NEED this.

[agneum]

We're Q3 2018, any updates???

At the very least, can we get an explanation why even at all the connector cares about what's going on in the database layer? For updates/deletes it's primary concern should be to find the primary key, else seems completely unrelated.

[briantbailey]

When a CRUD operation is executed the command being sent includes the OUPUT clause. While normally all we think about caring about is the primary key being returned there may be a number of fields that are set during this process (for example, default dates) which PowerApps then wants to remain in synch with the DB. I am assuming that they use this approach to make the calls more efficient as only one call is executed using this method. The obvious drawback is that it cannot handle the triggers. In order to make a change to allow triggers there would have to be a redesign of the connector and the principal means by which the data in the entity/object is kept in synch. This could affect performance (multiple calls to the database) in order to accomodate triggers.

On a side note, if your SQL Server instance allows for Change Data Capture, this may be a viable alternative to triggers or moving to stored procedures. I have not tested it, but am looking at it to try and get around this issue without a bunch more code in Flow (or PowerApps).

[briantbailey]

When a CRUD operation is executed the command being sent includes the OUPUT clause. While normally all we think about caring about is the primary key being returned there may be a number of fields that are set or modified during this process (for example, default dates, function calls on columns) which PowerApps then wants to remain in synch with the DB. I am assuming that they use this approach to make the calls more efficient as only one call is executed using this method. The obvious drawback is that it cannot handle the triggers. In order to make a change to allow triggers there would have to be a redesign of the connector and the principal means by which the data in the entity/object is kept in synch. This could affect performance (multiple calls to the database) in order to accomodate triggers.

On a side note, if you are using triggers for auditing and your SQL Server instance allows for Change Data Capture, this may be a viable alternative to triggers or moving to stored procedures. I have not tested it, but am looking at it to try and get around an issue without a bunch more code in Flow or PowerApps.

[SmartMeter]

I got over this bump in the road by using a "staging" table for my data, and sweeping this with a proc that inserted into the proper table. The table bound function was the sticky wicket. Fortunately we don't use these features often, but it was a pain, and delayed my implementation by a week! Thanks for the update!

[charlied_mc]

Any update on this? It's been almost 2 years at this point, we really should be able to use triggers in this way.

[FBSEstimator]

Can we get an update on this timeline.  It has now been over 2 years.  This is a pretty annoying thing that I need to run a flow to run a stored procedure rather than letting the database do the work.

[OasisSystemAdm]

I guess its about time?

Me --> Slams head on a wall!!

[Anonymous]

Helllloooooo??????

@SameerCh 

[SmartMeter]

I don't think it's a priority item to MS. Hope you can find an alternative workaround in your case. 

Stay safe, and good luck!

[SameerCh]

Hi Folks,

This is a really old thread.  Unfortunately, we still have the same limitations.  The challenge with Server side SQL triggers is precisely that we cannot find a way to get the data back.  And many of the experiences we have on Power Apps require that the data is sent back.

We now have a better workaround for at least some of the main use cases.  You can use a native SQL query to write the data to SQL. For example, you can use something like: INSERT INTO MyTable (Column1,...) VALUES ( value1, ...)

HTH

[SmartMeter]

With all due respect, "this is a really old thread" is a wee bit inappropriate response, and I cringed when I read it. It's a painful issue for a select few customers, which is WHY it's an old thread.  Let's be fair, a newbie already feels intimidated, and it's not very, um, uplifting to tell them they are responding to old threads. (It's kind of demeaning actually. )Their frustrated, they need a Microsoft buddy who asks them how they can help. That's why their venting.  Ask him for his SQL so you can check it out before making a suggestion. He might even Kudo you..or he might help find the fix for the problem. At least offer to give him a few screen shots of your suggestion.

Peace,  

@SmartMeter