Trigger to update foreign key field after insert on same table-CodePudding

I have two tables:

Table1 (surveyid [PKID], surveyname)
Table2 (visitid [PKID], surveyname, surveyid [FKID - refers to Table1]).

After inserting a new row into Table2, I would like to update Table2.surveyid with the surveyid from Table1, based on matching surveyname.

I thought it maybe wasn't possible (or good practice?) to create a trigger to update the same table. But I seem to have created a trigger that will do this. The problem is that after insert, the trigger updates the surveyid for every row, instead of just the newly inserted rows.

This trigger code works, but how do I ensure the trigger only updates the surveyid for newly inserted rows, and not all rows?

CREATE TRIGGER tr_update_table2_fk
ON Table2
AFTER INSERT
AS 
BEGIN
    UPDATE Table2
    SET surveyid = (SELECT t1.surveyid
                    FROM Table1 t1
                    WHERE t1.surveyname = Table2.surveyname)
END;

CodePudding user response：

Thank you MattM and DaleK, you've helped me figure out the answer. I was adding the inserted table into the subquery where clause before, instead of the query where clause. This does the trick:

CREATE TRIGGER tr_update_table2_fk
on Table2
AFTER INSERT
AS 
BEGIN
    UPDATE Table2 SET
        surveyid = (
            SELECT t1.surveyid
            FROM Table1 t1
            WHERE t1.surveyname = Table2.surveyname
        )
    WHERE Table2.visitid IN (SELECT visitid FROM inserted)
END;

CodePudding user response：

Yes, the inserted table is the answer.

I'd use it to capture the visitids of the inserted rows, then filter by them in a WHERE clause on the end of your UPDATE statement in your trigger.

E.g.

CREATE OR ALTER TRIGGER tr_update_table2_fk
    ON Table2 
    AFTER INSERT
AS 
BEGIN
    DROP TABLE IF EXISTS #VisitIds ;
    CREATE TABLE #VisitIds ( [id] INT ) ;

    INSERT INTO #VisitIds ( [id] )
    SELECT      [visitid]
    FROM        inserted ;

    UPDATE      Table2
    SET         [surveyid] = 
                (
                    SELECT      t1.[surveyid]
                    FROM        Table1 AS t1
                    WHERE       t1.[surveyname] = Table2.[surveyname]
                )
    WHERE       [visitid] IN ( SELECT [id] FROM #VisitIds ) ;
END
GO