2

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.

0

1 Answer 1

2

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

3

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.