Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Please describe the INSERT statement that is executing when this trigger is being hit. Are you doing an INSERT/SELECT or multiple INSERTS within a transaction, etc. How many rows at a time? –  TimG Apr 10 '13 at 14:11
    
added an update to my post –  booyaa Apr 10 '13 at 14:23
1  
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. –  ypercube Apr 10 '13 at 14:28
    
@ypercube cheers bud! I created a debug column held the row count for Foo and Inserted and added it to each new row I was creating in the trigger. You literally see Foo grow whilst Inserted remains a constant 1. Do you want to post an answer so I can accept it? If you're not fussed I'll do it later on. –  booyaa Apr 10 '13 at 14:41
add comment

1 Answer

up vote 2 down vote accepted

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

share|improve this answer
    
Are subselects (as used in the 2nd trigger example) as bad as people make them out to be? A lot of my colleagues dislike them and cursors because they aren't performant. –  booyaa Apr 10 '13 at 15:19
    
What are you referring to, to your second FROM Inserted a LEFT JOIN Foo b ON ...? I see nothing wrong with that. Or my NOT EXISTS subquery? –  ypercube Apr 10 '13 at 15:21
    
The NOT EXISTS subquery –  booyaa Apr 10 '13 at 15:29
    
This is a semi-join. I think SQL-Server optimizer is clever enough to identify the two ways of writing a semi-join (LEFT JOIN / IS NULL vs. NOT EXISTS) as equivalent. It's just a matter of preference how you write it. But you can always try (and test) both versions. –  ypercube Apr 10 '13 at 15:34
3  
This article has some performance tests: NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: SQL Server. But performance is highly affected by what indexes you have, the sizes of the tables and many other factors which you only can test. –  ypercube Apr 10 '13 at 15:41
show 2 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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