none
Trigger updates too many records RRS feed

  • Question

  • Below is a simple trigger to update a field within a newly inserted record. I'm in Visual Studio 2008, and when I execute an insert query (also below), the response is "2 rows affected by last query". Why 2 instead of one? Thanks a lot...sl.

    INSERT INTO Guests
                          (FirstName, LastName)
    VALUES     ('Bomber', 'Little')

    ALTER TRIGGER

     

    GuestAdded

    ON

     

    dbo.Guests

    FOR INSERT

    AS

    IF EXISTS

     

    (SELECT intIdentity FROM inserted)

     

    BEGIN

     

    DECLARE @intID uniqueidentifier

     

    SET @intID = (SELECT intIdentity FROM inserted)

     

    UPDATE dbo.Guests

     

    SET DupeKey = (FirstName + LastName)

     

    WHERE intIdentity = @intID

     

    END

    Wednesday, June 17, 2009 10:33 PM

Answers

  • This can be really streamlined to the following statement

    ALTER

    TRIGGER GuestAdded ON dbo.Guests
    FOR INSERT
    AS
    UPDATE
    G
    From dbo.Guests G
    SET DupeKey = (I.FirstName + I.LastName)
    INNER JOIN Inserted I
    On I.intIdentity = G.intIdentity
    END

    Be aware that triggers are fired per statement NOT per row. The trigger will be fired regardless if no or multiple rows are affected, you will have to handle that in your procedural logic. This can be best done with setbased operations.

    Btw, why dont you use a computed column in that case, if you are just doing a concatenation to another column ?

    -

    Jens


    Jens K. Suessmeyer http://blogs.msdn.com/Jenss
    Wednesday, June 17, 2009 10:55 PM
    Moderator