cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
stavrinio85
Level: Powered On

Issues when changing a number in database through powerapps which calls a trigger procedure

Untitled.png

USE [BOC_DEMO]
GO
/****** Object: Trigger [dbo].[After_Update_ToClose] Script Date: 12/5/2018 9:00:48 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE TRIGGER [dbo].[After_Update_ToClose] ON [dbo].[NumBranches]
AFTER UPDATE AS

DECLARE @num INT;
BEGIN


SET @num = ( select NumOfBranchesToClose from NumBranches);


CREATE TABLE [dbo].[tmp_toClose1](
[Branch] [nvarchar](255) NOT NULL,
[Lat] [float] NULL,
[Lon] [float] NULL,
[Address] [nvarchar](255) NULL,
[Town] [nvarchar](255) NULL,
[District] [nvarchar](255) NULL,
[NumofServices] [float] NULL,
[Profit] [float] NULL,
[Action] [nvarchar](255) NULL,
[CloseFlag] [nvarchar](200) NOT NULL,
PRIMARY KEY CLUSTERED
(
[Branch] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[tmp_toClose2](
[Branch] [nvarchar](255) NOT NULL,
[Lat] [float] NULL,
[Lon] [float] NULL,
[Address] [nvarchar](255) NULL,
[Town] [nvarchar](255) NULL,
[District] [nvarchar](255) NULL,
[NumofServices] [float] NULL,
[Profit] [float] NULL,
[Action] [nvarchar](255) NULL,
[CloseFlag] [nvarchar](200) NOT NULL,
PRIMARY KEY CLUSTERED
(
[Branch] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

--Select @num= NumOfBranchesToClose from NumBranches;
-- Insert statements for trigger here
--DELETE FROM NumBranches

DELETE FROM [dbo].[Close_Branches]
OUTPUT deleted.* INTO tmp_toClose1

--Output Delete.* into tmp_toClose

Insert into Close_Branches
output inserted.* into tmp_toClose2
SELECT top (@num) *
from Branches b
order by [Profit]

Drop Table tmp_toCLose1
Drop Table tmp_toClose2
END

 

From the powerapps i update the numbranches table which has one property as a pk.

as soon as this is updated it triggers the above code to update another table.

 

i get this error message.

Anyone has experience?

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Issues when changing a number in database through powerapps which calls a trigger procedure

Hi @stavrinio85

I understand how much of a problem this is.

The general workaround is to create a SQL Stored Procedure that carries out your data operation. In your example, this Stored Procedure would update [dbo].[NumBranches] and update the records in [dbo].[Close_Branches] using your business logic. You would then call this Stored Procedure via Flow, rather than update the table directly in PowerApps.

4 REPLIES 4
Super User
Super User

Re: Issues when changing a number in database through powerapps which calls a trigger procedure

Hi @stavrinio85

Unfortunately, PowerApps doesn't support tables with Triggers.

The 'known issues and limitations' section of the SQL Connector help page provides a little bit more detail.

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

stavrinio85
Level: Powered On

Re: Issues when changing a number in database through powerapps which calls a trigger procedure

So there is no way to make changes in the datasets in sql db by changing a single table and then triggering events to make the rest of the changes?
That seems unlikely not just a limitation but rather an issue.
What is the workaround with the flow you mentioned? How does that work?
I am way into the powerapp solution right now for me to drop the whole powerbi_powerapp thing.
Need to find a solution
Super User
Super User

Re: Issues when changing a number in database through powerapps which calls a trigger procedure

Hi @stavrinio85

I understand how much of a problem this is.

The general workaround is to create a SQL Stored Procedure that carries out your data operation. In your example, this Stored Procedure would update [dbo].[NumBranches] and update the records in [dbo].[Close_Branches] using your business logic. You would then call this Stored Procedure via Flow, rather than update the table directly in PowerApps.

Administrator
Administrator

Re: Issues when changing a number in database through powerapps which calls a trigger procedure

Thank you for your help with this post and the suggested workaround @timl

 

@TopShelf-MSFT

Helpful resources

Announcements
firstImage

PowerApps Monthly Community Call!

Join us next Wednesday for our Demo Extravaganza, October 16, 2019 8am PDT.

firstImage

Microsoft Business Applications Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

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

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

SecondImage

Power Platform Summit North America

Register by September 5 to save $200

Top Kudoed Authors
Users Online
Currently online: 375 members 4,657 guests
Please welcome our newest community members: