cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
stapes
Post Prodigy
Post Prodigy

SQL Triggers not working with Canvas App

I am trying to use an SQL trigger in conjunction with my app.

i am doing this in the OnChange property of a field:

 

    If(!IsBlank(txtActualQtyDelivered.Text), Patch('[dbo].[TBL_JS_GOODS_IN_CONTROLSHEET]', First(Filter('[dbo].[TBL_JS_GOODS_IN_CONTROLSHEET]',ID=SelectedItem.ID)),{ActualQtyDelivered:Value(txtActualQtyDelivered.Text), UpdatedBy:User().FullName,UpdatedOn: Now()})); Set(Updated, true)

 

Without using a trigger, this is fine, but when I add my trigger, I get a bunch of error message:

 

GoodsInV2 Screen4 - Inspection Tab - trigger error.jpg

The target table 'blah' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without an INTO clause.

 

This is my trigger below. As you can see, it does NOT contain an OUTPUT clause without an INTO clause.

 

I have had to remove my trigger for the time being so I can get my app working.

 

Can I assume that Triggers do not work with PowerApps?

 

   

USE [GoodsIn]
GO
/****** Object: Trigger [dbo].[UpdatesToControlSheet] Script Date: 28/11/2019 14:39:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

create Trigger [dbo].[UpdatesToControlSheet] ON [dbo].[TBL_JS_GOODS_IN_CONTROLSHEET]
FOR UPDATE
AS
BEGIN

-- this is what we are missing in our update!

declare @ID int
declare @stQtyonOrder varchar(2)
declare @thisTime datetime
set @ID = (Select [ID] from [dbo].[TBL_JS_GOODS_IN_CONTROLSHEET] where UpdatedOn > dateadd(SECOND,-30,getdate()))

set @stQtyonOrder = (Select [stQtyonOrder] from [dbo].[TBL_JS_GOODS_IN_CONTROLSHEET] where [ID] = @ID)

DECLARE @RunStoredProcSQL VARCHAR(1000);
DECLARE @tlFolioNum NVARCHAR(128) --int
DECLARE @tlQtyPicked NVARCHAR(128) --float
DECLARE @tlLineNo NVARCHAR(128) --int
DECLARE @PositionId NVARCHAR(128) --int

SET @tlFolioNum = (select thFolioNum from dbo.TBL_JS_GOODS_IN_CONTROLSHEET where ID = @ID)
SET @tlLineNo= (select tlLineNo from dbo.TBL_JS_GOODS_IN_CONTROLSHEET where ID = @ID)
SET @tlQtyPicked = (select ActualQtyDelivered from dbo.TBL_JS_GOODS_IN_CONTROLSHEET where ID = @ID)
SET @PositionId = (select PositionId from dbo.TBL_JS_GOODS_IN_CONTROLSHEET where ID = @ID)
Set @thisTime = GETDATE()
SET @RunStoredProcSQL = 'exec [Exchequer].[dbo].[uspDetails_update] ' + @tlFolioNum + ',' + @tlLineNo + ',' + @tlQtyPicked + ',' + @PositionId + ' '

EXEC (@RunStoredProcSQL); --AT [SHIRE-HPSPARE];

-- SS notes:

-- if ActualQtyDelivered = stQtyonOrder
-- set PDN-Matched = 1
-- tlQtyDel = ActualQtyDelivered
-- PDN-CreatedOn = Now()

IF @tlQtyPicked = (select stQtyonOrder from dbo.TBL_JS_GOODS_IN_CONTROLSHEET where ID = @ID)
BEGIN
UPDATE dbo.TBL_JS_GOODS_IN_CONTROLSHEET

SET [PDN-Matched] = 1
, [tlQtyDel] = @tlQtyPicked
--, [PDN-CreatedOn] = @thisTime
, [UpdatedBy] = 'trigger'
OUTPUT inserted.ID,
inserted.[PDN-Matched],
--Convert(DateTime, inserted.[PDN-CreatedOn]),
inserted.tlQtyDel
INTO dbo.TBL_JS_GOODS_IN_CONTROLSHEET_Trigger
WHERE ID = @ID
END
ELSE
BEGIN
DECLARE @SumtlQtyDel float
SET @SumtlQtyDel = (select tlQtyDel from dbo.TBL_JS_GOODS_IN_CONTROLSHEET where ID = @ID)

-- SS Notes:

-- if ActualQtyDelivered + tlQtyDel >= (Greater than or equal to) stQtyonOrder
-- set PDN-Matched = 1
-- [tlQtyDel] = @tlQtyPicked + [tlQtyDel] (? sum of just delivered qty & already delivered qty?)

IF (@tlQtyPicked + @SumtlQtyDel) >= @stQtyonOrder
BEGIN
UPDATE dbo.TBL_JS_GOODS_IN_CONTROLSHEET
SET [PDN-Matched] = 1
, [tlQtyDel] = @tlQtyPicked + [tlQtyDel]
--, [PDN-CreatedOn] = @thisTime
, [UpdatedBy] = 'trigger'
OUTPUT inserted.ID,
inserted.[PDN-Matched],
--Convert(DateTime, inserted.[PDN-CreatedOn]),
inserted.tlQtyDel
INTO dbo.TBL_JS_GOODS_IN_CONTROLSHEET_Trigger
WHERE ID = @ID
END
END
END

 

This article seems to confirm that Triggers can not be used with PowerApps:

 

https://powerusers.microsoft.com/t5/Building-Power-Apps-Formerly/Can-I-use-PowerApps-on-an-Azure-DB-...

 

Have managed to get this working using an SQL Stored Procedure to do the Update.

1 ACCEPTED SOLUTION

Accepted Solutions
timl
Super User
Super User

Hi @stapes 

Yes, what you've found is correct. PowerApps does not support tables with triggers. This applies to SQL Server, and also to Oracle databases.

View solution in original post

3 REPLIES 3
timl
Super User
Super User

Hi @stapes 

Yes, what you've found is correct. PowerApps does not support tables with triggers. This applies to SQL Server, and also to Oracle databases.

View solution in original post

v-xida-msft
Community Support
Community Support

Hi @stapes ,

I found that you have post same issue within this forum already, please check my response within the following thread:

https://powerusers.microsoft.com/t5/Building-Power-Apps-Formerly/How-do-I-prevent-runtime-errors-fro...

 

Actually, it is an known limit with SQL Table connector. Insert and update to a table does not work if you have a SQL server side Trigger defined on the table. Please check the following known limits with SQL Server connector:

https://docs.microsoft.com/en-us/connectors/sql/#known-issues-and-limitations

 

Please consider remove the SQL Table connection from your app, then re-create a new connection to your SQL Table from your app, then try your formula again, check if the issue is solved.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks

 

The other post is not the same issue. In that post I am asking "How do I prevent run time errors from breaking my code?" - I only gave this specific problem as an example. 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (1,179)