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

[SameerCh]

Hi @SmartMeter 

You are right!  I should have clarified the context. I was catching up on this thread and the workarounds provided. The old thread was meant to refer to the workarounds which were not there then. Sometimes in a hurry to post the solution, we fail to explain things properly. Appreciate you pointing this out.

The problem is very real - and the most frustrating part is that there is no easy workaround.  The ones suggested earlier (Stored Procs, or remove the SQL Triggers) are very invasive in that it requires one to modify the SQL Server Database.  One of the main motivation for us to add support for the native SQL query execution was essentially to get around problems like this.  It does take a little bit of understanding basic SQL query though. 

And I actually did try this out before I responded.  The reason I didn't include the screenshot is because I don't see a lot of value. But this is how it looks like anyway:

HTH

Sameer

[SmartMeter]

Thank you Sameer for taking my comment professionally as intended, and for posting a most helpful response for others who will encounter the issue in time. I respect your answer, and I feel for your side of the equation. This problem was a real time killer in my case, but knowing SQL intimately, it was pretty easy to dig my way out of trouble. A lot of users are not SQL savvy, and they need all the help our mothership is willing to provide here. As for the rest of us "users" please don't be rude to Microsoft, this is a known issue and they will eventually fix it, but realize it is making PowerAutomate essentially run code on your database so it's certainly a challenge on their side to provide a secured means to do this.  We are asking them to "grant execute on * to MyGatewayAccount" which is quite insane to secure when you think about it. Thanks again for accepting professional criticism. (Now go cure Coronavirus !)

Peace.

[Gjakova]

Hi there @SmartMeter ,

How did you solve this issue? With the answer that @SameerCh provided or did you use something else?
We also have a form which we want to update in SQL (our table has triggers), but since it won't work with triggers we have to look for a solution. Hope to hear from you (both). Thanks!

[SmartMeter]

Hi @Gjakova Glad to help. 

In my case I created a small table to stage my data in and directly inserted into this staging table. I then wrote a stored procedure to shuttle the data into the final destination and ran this on an agent job to a clock schedule. It worked out fine for me, but I admit the "snappiness" of a transaction was not preserved, and the customer had to review their updates later on. I can see why Microsoft doesn't want to cascade code threads, considering all the virus in the world today. Speaking of Virus, stay safe but don't forget to live your life in spite of nature. Peace, and happy SQL work to you my friend!

[TonyCasilla]

I have this same problem in 2023 it is disappointing to see that after almost 7 years they cannot solve this problem

[TonyCasilla]

[Gjakova]

hi @TonyCasilla in my case I wanted to create a history table with triggers. So if you deleted something from one of my tables, that row gets movied to another table for auditing/history. The way I solved it was by using system versioned tables in SQL. Matbe that also helps for you? (If you have the same usecase).

[Vic_Luna]

Hi

I see your recommendation, but I can not see how I'm going to run this store procedure if I can no trigger with an action.

May you help me?

Regards