cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
yoshihirok
Level 10

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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft Employee

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

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

View solution in original post

25 REPLIES 25
Microsoft Employee

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

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
Level 8

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

@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
Not applicable

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

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

Highlighted
Harm
Level: Powered On

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

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
Level: Powered On

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

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
Not applicable

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

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

Microsoft Employee

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

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

Microsoft Employee

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

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
Level: Powered On

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

What about stored procedures for on prem, any news?
Microsoft Employee

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

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
Level 10

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

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

Microsoft Employee

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

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
Level 10

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

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

Microsoft Employee

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

@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
Level 10

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

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

Microsoft Employee

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

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
Level 10

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

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.

Microsoft Employee

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

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

View solution in original post

rgparisoto
Level: Powered On

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

Hi guys!

Any news about it?

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Users Online
Currently online: 181 members 4,504 guests
Please welcome our newest community members: