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

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.

CodePudding user response：

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.

CodePudding user response：

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!