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

[stavrinio85]

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?

[timl]

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]

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

[timl · Accepted solution]

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.

[Anonymous]

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

@Anonymous