Skip to main content

Notifications

Power Automate - Using Connectors
Answered

Insert into SQL table with active triggers implemented

Like (3) ShareShare
ReportReport
Posted on 4 Jul 2017 10:15:04 by 40

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

  • Joao Sousa Profile Picture
    Joao Sousa 2 on 04 Oct 2024 at 13:24:39
    Insert into SQL table with active triggers implemented
    bump into the latest question. 
     
    Has been around 7 yrs? Any updates on it?
     
  • SS-04091039-0 Profile Picture
    SS-04091039-0 2 on 04 Sep 2024 at 10:40:50
    Insert into SQL table with active triggers implemented
    Did power platform fixed the issue with using sql tables with triggers ?
  • Vic_Luna Profile Picture
    Vic_Luna 2 on 24 Jul 2023 at 19:11:44
    Re: Insert into SQL table with active triggers implemented

    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

  • Gjakova Profile Picture
    Gjakova 180 on 24 Feb 2023 at 07:10:47
    Re: Insert into SQL table with active triggers implemented

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

  • TonyCasilla Profile Picture
    TonyCasilla 47 on 24 Feb 2023 at 05:12:02
    Re: Insert into SQL table with active triggers implemented

    TonyCasilla_0-1677215511789.png

     

  • TonyCasilla Profile Picture
    TonyCasilla 47 on 24 Feb 2023 at 04:25:11
    Re: Insert into SQL table with active triggers implemented

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

  • SmartMeter Profile Picture
    SmartMeter 248 on 09 Feb 2021 at 16:25:15
    Re: Insert into SQL table with active triggers implemented

    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!

  • Gjakova Profile Picture
    Gjakova 180 on 09 Feb 2021 at 15:55:56
    Re: Insert into SQL table with active triggers implemented

    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 Profile Picture
    SmartMeter 248 on 06 Apr 2020 at 21:08:26
    Re: Insert into SQL table with active triggers implemented

    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.

  • SameerCh Profile Picture
    SameerCh on 06 Apr 2020 at 20:53:29
    Re: Insert into SQL table with active triggers implemented

    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

     

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

End of Year Newsletter…

End of Year Community Newsletter…

Tuesday Tip #12 Start your Super User…

Welcome to a brand new series, Tuesday Tips…

Tuesday Tip #11 New Opportunities…

Welcome to a brand new series, Tuesday Tips…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 144,696

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,456

Leaderboard

Featured topics

Loading complete