How to use Insert Trigger on Azure SQL Database with 'Azure SQL' insert row ?

[yoshihirok]

I want to use Insert Trigger on the Table on Azure SQL Database,
for setting the DateTime from Twitter's 'CreatedAt' Text.

Fail Message of log in "Insert Row".

Body{
  "status": 500,
  "message": "The target table 'dbo.Twitter' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause.",
  "source": "127.0.0.1"
}

My Trigger is,

CREATE TRIGGER TwitterInsert ON Twitter
AFTER INSERT
AS
BEGIN
	UPDATE Twitter
	SET CreatedDateTime = DATEADD(hour, 9, 
		CONVERT(DATETIME, SUBSTRING(inserted.CreatedAt, 5, 7) + SUBSTRING(inserted.CreatedAt, 27, 4) + SUBSTRING(inserted.CreatedAt, 11, 9)))
	FROM inserted
	WHERE Twitter.id = inserted.id AND LEN(inserted.CreatedAt) = 30
END

I need to create the DateTime column and some related tables for using DirectQuery to Azure SQL Database by Power BI,

for real-time report without hourly reload data.

Regards,

Yoshihiro Kawabata

[jeffreed]

Thank you for raising this issue. Due to a bug, which we are now working on, It is not possible to use the SQL insert row action on a table with a trigger. It should be possible soon. For now to use the action you will need to remove the trigger.

-jeffreed msft

[hintbw-abc]

@jeffreed - Any update on this bug now that it is 2 months down the road. Is it still not possible to use a trigger on a table with a SQL insert row action?

[Anonymous]

Hello.

What about bug when trying to use triggers?

Still it is not possible to use a database trigger.

INSERT/UPDATE/DELETE does not work if the trigger is enabled.

Please let us know is this fix planned and when?

Thanks a lot beforehand

[Harm]

I am having the same problem.

I see many oppertunities for flow in cobination, but all tables in my solution are equiped with triggers.

Could please give an update on current status of developent

[datenwerkstatt]

I did a blog post on this issue. So far neither triggers nor stored procedures (On-premise) seem to work.

https://selfservice-bi.com/2016/11/25/solving-real-world-scenarios-with-microsoft-flow-challenge-no-...

Regards

Andreas

[Anonymous]

@jeffreed wondering if there is a schedule for resolving this issue?

[jeffreed]

The issue I raised was resolved as fixed in October.  I am checking in with the team, but it seems to be fixed. Can you verify if it is working in your specific scenario?

-jeff

[jeffreed]

More info: The issue still exists and is pretty tricky since insert trigger can change the row it is unreliable, using the current method, to return the correct insertion information.  It is being tracked, but there is not ETA on a change to make it work.

-jeff

[datenwerkstatt]

What about stored procedures for on prem, any news?

[Cameron]

Regarding Stored Procedures support for on-prem connections:

Yes, this feature is very important to us strategically, and it is scheduled for development in the next few weeks!

You will hear the shouts of joy when it becomes available. 🙂

Thanks,

-Cameron

[Meneghino]

Hello all, can we now use Flow to create a record in an Azure SQL DB table with a trigger?
Thanks.

[Cameron]

I realize this is a side note - but on-prem Stored Procedures are now available for use in the SQL Connector. 

The main subject of this thread is still an issue - but will be somewhat mitigated by the fact that top-level triggers will soon be available in PowerApps / LogicApps for SQL Azure. This will allow you to trigger an action based on a database insert or update, and may mitigate the need for server-side triggers.

Thanks,

-Cameron

[Meneghino]

Hi @Cameron, thanks for the update.  When you say 'soon', are we talking days/weeks/months?

Also, would the timeout also be an issue if an insert triggers a stored procedure lasting >2 minutes? (currently timeout seems to be set 120 seconds).

Please see this thread:

https://powerusers.microsoft.com/t5/Using-Flows/Flow-with-Azure-function-quot-timeout-was-reached-qu...

[Cameron]

@Meneghino- The feature is actually available in one production region currently (EASTUS2), and will be more widely available after some production experience. That's the best I can give you right this minute. 

As for the timeout issue - this is still a problem, but there is a feature in the works to provide "fire-and-forget" capability to stored procedure calls - this will essentially trigger the call and return immediately, but allow processing to continue on the server side. Obviously, this means you cannot rely on the output of the stored procedure, since the connector will not be waiting for it. Would this satisfy your needs?

Thanks,

-Cameron

[Meneghino]

Thanks for the reply @Cameron

Relying on the output of stored procedures is quite important in some cases but not all.  I have both, and the latter is when the stored procedure takes a long time (> 10 minutes) for housekeeping tasks.  These cannot be scheduled as they need to happen on demand.

I would suggest an additional optional parameter in the stored procedure call, either a boolean parameter to say whether the call is fire and forget or not (default being the latter) or if you really want to be sophisticated then a numerical parameter for timeout in seconds, where the default is 120 or whatever sytem-wide paramter you have.

Hope this helps.

Best regards,

Meneghino

[Cameron]

Meneghino, There is no requirement that your stored procedures be scheduled - they can certainly be triggered in a flow based on any previous 'triggerable' action. And yes, what you described is essentially the design of "fire and forget". This would allow the connector service to free up resources specific to that task and allow processing to occur on the backend (SQL in this case) -- most likely returning a 202 (accepted) to indicate that the operation to start succeeded, but it has not yet completed. It is very important that the connector quickly move to the next request to ensure scalability and responsiveness for all users, which is why we cannot simply increase the timeout. Thanks, -Cameron

[Meneghino]

Thanks @Cameron

UPDATE: Just tested the below again and it does NOT seem to work.  The last retry seems to stop the procedure, is there any way that you could just let the procedure continue as fire-and-forget after the last try?

INCORRECT:

Actually, there is work-around to get fire-and-forget today already.  I just let the procedure call from Flow time out and repeat, once Flow is tired of repeating (i.e. 4 retries = approx. 10 minutes) then the stored procedure finally completes.

Of course this is not ideal, but I have altered my stored procedure to expect a few calls before being allowed to finish.

Thanks.

[Cameron · Accepted solution]

Yes, I see that is not ideal. When we implement fire-and-forget, this will benefit your scenario significantly. We are currently planning this feature to ensure that long-running operations do not take endless resources on the server, in order to ensure that in this multi-tenant system, all users have equal opportunity for processing power. We will announce the feature when it is ready, but it is likely two or more months away, just FYI. Thanks, -Cameron

[rgparisoto]

Hi guys!

Any news about it?