Sql-server – What would cause extra records to be inserted during a INSERT trigger

sql servertrigger

The following trigger that joins Inserted table on itself creates multiple unwanted records:

CREATE TRIGGER [dbo].[trFooInsert] on [dbo].[Foo]
AFTER INSERT
AS 
BEGIN
    IF EXISTS(SELECT 1 FROM Inserted a
                LEFT JOIN Inserted b ON b.Flag = 1 AND a.ID = b.ID
                WHERE b.Flag IS NULL AND a.Flag = 0
                ) -- exists
    BEGIN
        INSERT INTO Foo(ID, Etc, Flag)              
        SELECT a.ID, a.Etc, 1 FROM Inserted a
            LEFT JOIN Inserted b ON b.Flag = 1 AND a.ID = b.ID
            WHERE b.Flag IS NULL AND a.Flag = 0
    END -- insert
END -- trigger

Where as this trigger that joins Inserted on Foo (the table that the trigger is linked to) creates one record.

CREATE TRIGGER [dbo].[trFooInsert] on [dbo].[Foo]
AFTER INSERT
AS 
BEGIN
    IF EXISTS(SELECT 1 FROM Inserted a
                LEFT JOIN Foo b ON b.Flag = 1 AND a.ID = b.ID
                WHERE b.Flag IS NULL AND a.Flag = 0
                ) -- exists
    BEGIN
        INSERT INTO Foo(ID, Etc, Flag)              
        SELECT a.ID, a.Etc, 1 FROM Inserted a
            LEFT JOIN Foo b ON b.Flag = 1 AND a.ID = b.ID
            WHERE b.Flag IS NULL AND a.Flag = 0
    END -- insert
END -- trigger

The second trigger does exactly what I want, but I'm trying why the first trigger creates so many records. I would've expected Inserted and Foo to be identical when the trigger fires. Is there any kind of special property/behaviour that the Inserted table exhibits when joining on itself.

Apologies if there's not enough info, I've tried to reduce to the basic differences between the two triggers.

Update #1

I'm trying to backfill the table with records where the user account has transactions, but doesn't have the flag set.

Update #2

The inserts are carried out by a C# app. The data source is flat file where each line represents a single row in the table. I don't know if the app uses connection pooling or if this matters. The bulk inserts are done once a month. The C# app usually imports 8373 rows. The 1st trigger inserts 50 additional row, there's only one row that I want.

Best Answer

First, you don't need the IF EXISTS part in your trigger code. You have the exact same code in the second part so if there are 0 rows produced, you'll be inserting 0 rows anyway:

CREATE TRIGGER [dbo].[trFooInsert] on [dbo].[Foo]
AFTER INSERT
AS 
BEGIN
    INSERT INTO Foo(ID, Etc, Flag)              
    SELECT a.ID, a.Etc, 1 FROM Inserted a
        LEFT JOIN Foo b ON b.Flag = 1 AND a.ID = b.ID
        WHERE b.Flag IS NULL AND a.Flag = 0
END ; -- trigger

which can also be written as:

CREATE TRIGGER [dbo].[trFooInsert] on [dbo].[Foo]
AFTER INSERT
AS 
BEGIN
    INSERT INTO Foo(ID, Etc, Flag)              
    SELECT a.ID, a.Etc, 1 
        FROM Inserted a
        WHERE a.Flag = 0
          AND NOT EXISTS
              ( SELECT *
                FROM Foo b 
                WHERE b.Flag = 1 AND a.ID = b.ID
              )
END ; -- trigger

So, you want any insert on the table with Flag=0 to also trigger another insert, with same data but Flag=1 (unless there is already a row in Foo with that ID and Flag=1.

Second, and what the actual problem is about, Inserted is a special table with all the (just) "inserted" rows, not the full table you inserted into. It obviously has less (than or equal) rows as Foo after the insert. So a join to it will not give same results as a join to Foo.

The trigger that produces the multiple unwanted rows had Inserted b instead of Foo b so it does this:

For any insert on the table with Flag=0 it also triggered another insert, with same data but Flag=1 (unless this same insert had such a row inserted).

You can read more about the special Inserted and Deleted tables at the MSDN:
How to use the inserted and deleted Tables

Related Question