CODEWITHSUNDEEP
sqlsql-serverstored-procedurestriggersssms
PWozcyechowski
PWozcyechowski

Reputation: 3

Replacing a trigger with a stored procedure

I'm trying to replace a trigger statement with a stored procedure since enabled triggers are not allowed when using the tables in microsoft powerapps.

Simplified, I have to tables:

Currently, for every new record in KPI_Dim my trigger adds a new row in KPICurrent_Fact with the FK and an autoincremented PK. The rest of the columns e.g. KPI_Value are supposed to be empty. My simple trigger looks like this:

CREATE TRIGGER [dbo].[trg_insert_newKPI] 
ON [dbo].[KPI_Dim]
FOR INSERT AS
INSERT INTO KPICurrent_Fact (KPI_key)
SELECT KPI_ID
FROM INSERTED

Now, I want to create a stored procedure that can achieve exactly the same. I have tried to find a solution myself but I'm new to stored procedures and could not find anything that would replicate a trigger.

I'm using SSMS v.18.4.

Thank you for any suggestions.

EDIT

Added Table creation and insert into statement code.

/* Create KPI_Dim table*/

CREATE TABLE [dbo].[KPI_Dim](
    [KPI_ID] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
    [KPIName] [varchar](200) NOT NULL,
    [KPIDescription] [varchar](500) NULL,
    [KPIGroup] [varchar](100) NOT NULL,
    [KPISubGroup] [varchar](100) NULL,
    [KPIOwner] [varchar] (50) NOT NULL,
    [DateCreated] DATETIME  NULL DEFAULT(GETDATE())
    )


  /* Example data */
INSERT INTO [dbo].[KPI_Dim]
    (
    KPIName, 
    KPIDescription,
    KPIGroup,
    KPISubGroup,
    KPIOwner 
    )
VALUES 
    ('TestKPIName','testtest','TestGroup', 'TestSubGroup', 'TestOwner');

Upvotes: 0

Views: 307

Answers (1)

Venkataraman R
Venkataraman R

Reputation: 12969

You can go for OUTPUT Clause and insert into table variable. From the table variable, you can insert into fact table.

CREATE TABLE [dbo].[KPI_Dim](
    [KPI_ID] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
    [KPIName] [varchar](200) NOT NULL,
    [KPIDescription] [varchar](500) NULL,
    [KPIGroup] [varchar](100) NOT NULL,
    [KPISubGroup] [varchar](100) NULL,
    [KPIOwner] [varchar] (50) NOT NULL,
    [DateCreated] DATETIME  NULL DEFAULT(GETDATE())
    )
CREATE TABLE dbo.KPI_Fact
(
 [KPI_ID] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
 [KPIDIMID] INT NULL FOREIGN KEY references [dbo].[KPI_Dim]([KPI_ID])
)

DECLARE @inserted table(KPI_DIMID INT)

INSERT INTO [dbo].[KPI_Dim]
    (
    KPIName, 
    KPIDescription,
    KPIGroup,
    KPISubGroup,
    KPIOwner 
    )
OUTPUT inserted.KPI_ID INTO @inserted
VALUES 
    ('TestKPIName','testtest','TestGroup', 'TestSubGroup', 'TestOwner');

INSERT INTO dbo.KPI_Fact([KPIDIMID])
SELECT * FROM @inserted
KPI_ID KPIDIMID
1 1

Upvotes: 1

Related Questions