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
Solved! Go to Solution.
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
@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?
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
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
I did a blog post on this issue. So far neither triggers nor stored procedures (On-premise) seem to work.
Regards
Andreas
@jeffreed wondering if there is a schedule for resolving this issue?
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
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
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
Hello all, can we now use Flow to create a record in an Azure SQL DB table with a trigger?
Thanks.
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
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:
@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
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
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.
Hi guys!
Any news about it?
User | Count |
---|---|
38 | |
35 | |
15 | |
13 | |
12 |
User | Count |
---|---|
23 | |
20 | |
18 | |
13 | |
13 |