cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Continued Contributor
Continued Contributor

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
Super User III
Super User III

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
Super User III
Super User III

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

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

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
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Top Solution Authors
Top Kudoed Authors
Users online (11,993)