CODEWITHSUNDEEP
sql-serversql-server-2008database-trigger
Robert C
Robert C

Reputation: 60

SQL Server Insert trigger inserting more than once

My trigger is for some reason inserting 4 times, and the last one is only being grouped. Basically what I want to do is when information is inserted into my table tbl_delete_Panel I want to group this data and insert the grouping into tbl_delete_panel_Orderin.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE Trigger[dbo].[deletePanelSQL]
ON [dbo].[tbl_delete_Panel]
AFTER INSERT
AS
BEGIN
     /* Insert Summed Quantity for each stud or track into tbl_delete_panel for deletion. tbl_delete_panel trigger will be triggered */
    INSERT INTO tbl_delete_panel_Orderin (Part_Name, OrderID, SumOfQTY)
       SELECT 
           tbl_delete_panel.Part_Name, tbl_delete_panel.OrderID, 
           Sum(tbl_delete_panel.QTY) AS SumOfQTY
       FROM 
           tbl_delete_panel
       GROUP BY 
           tbl_delete_panel.Part_Name, tbl_delete_panel.OrderID;
END;

Upvotes: 1

Views: 126

Answers (1)

marc_s
marc_s

Reputation: 754518

Right now, you keep inserting the complete set of rows from the tbl_delete_panel, every time this trigger is fired.

What you should do instead is look at the Inserted pseudo table which can and will contain multiple rows (so you need to handle that situation, with a nice, proper set-based approach - no cursor, no while loops!) - and insert those rows (that were newly inserted by the statement that caused the trigger to fire) - not the whole table every time !

Try something like this:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE Trigger[dbo].[deletePanelSQL]
ON [dbo].[tbl_delete_Panel]
AFTER INSERT
AS
BEGIN
     /* Insert summed quantity for each stud or track into tbl_delete_panel for deletion. tbl_delete_panel trigger will be triggered */
    INSERT INTO tbl_delete_panel_Orderin (Part_Name, OrderID, SumOfQTY)
       SELECT 
           Part_Name, OrderID, 
           Sum(QTY) AS SumOfQTY
       FROM 
           Inserted i
       GROUP BY 
           Part_Name, OrderID;
END;

Upvotes: 1

Related Questions