Create stored procedure to generate multiple rows per row from another table

Question

I have previously used a trigger to create 13 new rows per row in a different table. This has worked great, but as I decided to give users of my PowerApps the option of adding a new row, I realized that I no longer can use a trigger, since PowerApps can't add rows to a table with a trigger..

I have tried to convert my trigger to a stored procedure, but I'm somehow stuck and can't see the way to solve this.

CREATE TRIGGER ProjectInsert_Test
ON Prodbudget_Test
AFTER INSERT
AS
BEGIN
    IF @@ROWCOUNT = 0 
       RETURN;

    SET NOCOUNT ON;

    MERGE ProdbudForecast_DQ_Test AS PP
    USING
        (SELECT
             I.Client, I.ClientProjectID,
             I.ProjectType,
             Period = DATEADD(MONTH, p.num, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)),
             I.Period2
         FROM 
             Inserted AS I
         CROSS JOIN 
             (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12), (228)) AS p(num)) AS PD ON PD.ClientProjectID =  PP.ClientProjectID
                                      AND PD.Period = PP.Period
    WHEN NOT MATCHED BY TARGET 
        THEN INSERT (Client, ClientProjectID, ProjectType, Period, Period2)
        VALUES (PD.Client, PD.ClientProjectID, PD.ProjectType, PD.Period, PD.Period2)

    WHEN NOT MATCHED BY SOURCE 
             AND PP.ClientProjectID IN (SELECT I.ClientProjectID FROM Inserted AS I)
         THEN DELETE;
END;

I have tried to remove the rowcount, but I need to reference my table (Prodbudget_Test) in a different way. I want to add 13 new rows per clientprojectid if it does not already exist in my destination table.

I have tried a method I found using

With periods (Period)
As (
Select dateadd(month, p.num, dateadd(month, datediff(month, 0, getdate()), 0))
From (Values (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11) ) As p(num)
    )
Insert Into ProdbudForecast_DQ_Test (Client, ClientProjectID, 
     ProjectType, Period, Period2
I.Client,
        I.ClientProjectID,
        I.ProjectType,
        Period = concat(FORMAT(p.[Period], 'yyy'), FORMAT(p.[Period], 'MM')),
        I.Period2
FROM periods p
Cross join Prodbudget_Test;

But this one I'm getting an error for dateformat and can't see how to implement skipping if already exists in my table..

Any help is greatly appreciated.

I have to ask, why does your object name start with `dbo.`? That is *really* confusing. — Thom A, Feb 21 '22 at 15:35
@Larnu, my apologies. removed this now as it is not needed here. — powerbi_2_sql, Feb 21 '22 at 16:31
Uhm, how would it *know* if a table had a trigger on it? DBAs can hide such things from user connections. — RBarryYoung, Feb 21 '22 at 18:13
@RBarryYoung the error message in powerapps is this: The target table 'dbo.Prodbudget' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause — powerbi_2_sql, Feb 21 '22 at 19:42
_I'm getting an error for dataformat_ Please [Edit](https://stackoverflow.com/posts/71209017/edit) your question to include the full and complete error message - as text, not screen shots. If I were to guess I'd say it's related to not specifying a target table and column list for `Insert Into`. — AlwaysLearning, Feb 21 '22 at 21:52
Also in the original code `Period` appears to be a `date` type but the second code block is trying to define `Period` as a string containing a (3 digit) Year and a Month but no Day component. Related to your "dataformat" error perhaps? — AlwaysLearning, Feb 21 '22 at 21:58

score 1 · Answer 1 · answered Feb 22 '22 at 07:39

1

I realized that I no longer can use a trigger, since PowerApps can't add rows to a table with a trigger..

This is not correct. When PowerApps creates a row in a table, the system creates sort of a stream. The event listener of your system sees that there is a new record and at that moment it triggers your secondary flow, i.e. your trigger sequence. PowerApps can add a row to your table.

answered Feb 22 '22 at 07:39

Iona Varga

509
2
8

hmm, how do you explain my error then? The target table 'dbo.Prodbudget' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause. Maybe there is something I'm doing wrong, but I have found a lot of posts on PowerApps community having similar issues. – powerbi_2_sql Feb 22 '22 at 11:09
Its a general SQL question, not PowerApps: https://stackoverflow.com/questions/13198476/cannot-use-update-with-output-clause-when-a-trigger-is-on-the-table Your query is just wrongly written. – Iona Varga Feb 22 '22 at 12:09
The manual states: Insert and update to a table doesn't work if you have a SQL Server side Trigger defined on the table. To workaround this issue, you can do either of the following: - Use a Stored Procedure or Native Query. - Remove the Trigger from your SQL table. With Native query it works fine. – Iona Varga Feb 23 '22 at 15:32

score 1 · Answer 2 · answered Feb 22 '22 at 21:21

Thanks to great replies from the community i fixed my code and I'm now successfully using this code:

With periods (Period)
As (
Select dateadd(month, p.num, dateadd(month, datediff(month, 0, getdate()), 0))
From (Values (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (228) 
 ) As 
p(num)
)
Insert Into ProdbudForecast_DQ_Test (Client, ClientProjectID, 
 ProjectType, Period, Period2
 I.Client,
    I.ClientProjectID,
    I.ProjectType,
    Period = p.[Period],
    I.Period2
   FROM periods p
  Cross join Prodbudget_Test
  where 
not exists (SELECT * FROM ProdbudForecast_DQ_Test c
    where concat(c.ClientProjectID, c.periode2) = concat(i.ClientProjectID, i.Periode2)
    );

Easy and simple, I just started out not identifying the real issue..

Thanks!

Create stored procedure to generate multiple rows per row from another table

2 Answers2