Using Trigger creates duplicates

Question

I have created a trigger to inert into table2 if a new row is inserted into table1 (at least I thought). The queries is like this:

CREATE TRIGGER project_insert
ON TABLE1
AFTER INSERT
AS
BEGIN
Insert into TABLE2 (ProjectID, Status, Period)
SELECT 
    ProjectID
  , Status
  , Period = concat(FORMAT(GETDATE(), 'yyy'), FORMAT(GETDATE(), 'MM'))
FROM TABLE1
WHERE Status in (1, 2)

This seems to work as it is inserting the project after I add it to table1, however everytime I add a new project it seems that the trigger inserts a new row for each project, I have in table1. I would like it to only add the new project.

Is this doable? Thanks!

You marked the wrong answer. Triggers should almost always refer to the virtual inserted and/or deleted tables. In this case, all the rows inserted by the firing statement can be found in the inserted table - no need to refer to the actual table (TABLE1 - nice name btw) on which the trigger is defined. The approach you marked is inefficient and does not scale well. And logically you should be concerned. If something removes a row in TABLE2 at some point for some reason, your "answer" will add it back. — SMor
– SMor, Commented May 6, 2021 at 14:48

Stephen Morris - Mo64 · Accepted Answer · 2021-05-06 14:18:38Z

1

You need to use the special virtual table INSERTED which represents the rows that have been inserted causing the trigger to fire and JOIN it back to your Table2 using the PK (ProjectID in this case I think)

Insert into TABLE2 (ProjectID, Status, Period)
SELECT 
    ProjectID
  , Status
  , Period = concat(FORMAT(GETDATE(), 'yyy'), FORMAT(GETDATE(), 'MM'))
FROM TABLE1 T
INNER JOIN INSERTED I ON
  T.ProjectID = i.ProjectID
WHERE Status in (1, 2)

answered May 6, 2021 at 14:18

Stephen Morris - Mo64

4,6761 gold badge10 silver badges18 bronze badges

3

Why do you need to join to TABLE1 (the table for the trigger)? Everything you need is in inserted.

SMor
– SMor

2021-05-06 14:44:31 +00:00
Commented May 6, 2021 at 14:44

Add a comment |

Stack Exchange Network

Using Trigger creates duplicates

1 Answer 1

Your Answer

Hot Network Questions

Using Trigger creates duplicates

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions