cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Lee
Kudo Collector
Kudo Collector

Insert into SQL table with active triggers implemented

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

23 REPLIES 23

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:

 
 
 

image.png

 

HTH

Sameer

 

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.

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!

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!

Helpful resources

Announcements
 WHAT’S NEXT AT MICROSOFT IGNITE 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Register for a Free Workshop.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

September Events 2022

Check out all of these events

Attend in person or online, there are incredible conferences and events happening all throughout the month of September.

Users online (3,622)