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.