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
Use the inserted
table, which is a special table available inside triggers containing the rows that will be updated/inserted into the table.
ALTER TRIGGER tr_app_bread_crumbs_afterupdate
ON [dbo].[app_bread_crumbs]
AFTER UPDATE
AS
BEGIN
UPDATE [dbo].[app_bread_crumbs]
SET last_updated_by = SYSTEM_USER,
last_updated_on = GETDATE()
FROM dbo.app_bread_crumbs abc
WHERE EXISTS (SELECT 1 FROM inserted i WHERE i.id = abc.id);
END
Best Answer
You are right, there is a virtual table called "inserted" which is what you would need to join on. This table contains all the rows which have been inserted in the original table insert statement. So something like: