Unanswered

Insert into SQL table with active triggers implemented

(3) Share

Report

Posted on by 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

Categories:

Using connectors

I have the same question (0)

All responses (29)

Answers (1)

v-yamao-msft on at

Like (1)

Report

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

Was this reply helpful? Yes No
Lee 40 on at

Like (1)

Report

Was this reply helpful? Yes No
Lee 40 on at

Like (1)

Report

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

Was this reply helpful? Yes No
Verified answer

Community member on at

Like (4)

Report

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

Was this reply helpful? Yes No
Lee 40 on at

Like (1)

Report

Hiya Sameer

yep thought so 🙂 pity

Already beat you to it,

Stored procedure armed and ready.

Flow now working as before.

cheers

Lee

Was this reply helpful? Yes No
SmartMeter 248 on at

Like (1)

Report

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

Was this reply helpful? Yes No
dhcinfotech 12 on at

Like (4)

Report

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

Was this reply helpful? Yes No
dhcinfotech 12 on at

Like (2)

Report

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

Was this reply helpful? Yes No
agneum 204 on at

Like (3)

Report

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.

Was this reply helpful? Yes No
Brian Bailey 8 on at

Like (2)

Report

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

Was this reply helpful? Yes No