SQL Server Performance

Help with an Update Trigger

Discussion in 'SQL Server 2008 T-SQL Performance Tuning' started by Clown Kiss, Jan 26, 2011.

  1. Clown Kiss New Member

    This is my Trigger:
    ALTER TRIGGER [dbo].[trig_onUpdateLocation]
    ON [dbo].[Location]
    FOR UPDATE
    AS
    declare @ID varchar(20)
    select @ID = (select LocationID from inserted)
    Update Location
    SET DateTimeLastChanged = GETDATE() where LocationID = @ID


    If I try to Update multiple records in the Location table, I get this error:
    Msg 512, Level 16, State 1, Procedure trig_onUpdateLocation, Line 14
    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
    The statement has been terminated.


    I understand why I am getting the error, however, I do not know how to modify the trigger in order to avoid this error.
    Any help would be greatly appreciate.
    Thanks,
    rjs
  2. FrankKalis Moderator

    [quote user="Clown Kiss"]
    This is my Trigger:
    ALTER TRIGGER [dbo].[trig_onUpdateLocation]
    ON [dbo].[Location]
    FOR UPDATE
    AS
    declare @ID varchar(20)
    select @ID = (select LocationID from inserted)
    Update Location
    SET DateTimeLastChanged = GETDATE() where LocationID = @ID


    If I try to Update multiple records in the Location table, I get this error:
    Msg 512, Level 16, State 1, Procedure trig_onUpdateLocation, Line 14
    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
    The statement has been terminated.


    I understand why I am getting the error, however, I do not know how to modify the trigger in order to avoid this error.
    Any help would be greatly appreciate.
    Thanks,
    rjs
    [/quote]
    Even though you understand why you get this error, it may be helpful for others searching the forum to explain a little more.
    You get this error, because you haven't take into account that the statement that caused the trigger to fire may affect more than one row. This is why it is imperative to be prepared for these cases. You would have to rewrite your statement like this (untested):
    UPDATE L
    SET L.DateTimeLastChanged = GETDATE()
    FROM
    Location L
    JOIN
    inserted I ON L.Id = I.Id
  3. Clown Kiss New Member

    Thank you!

Share This Page