Handle multiple rows in sql trigger update

Question

How can I handle multiple rows into a update trigger/how to modify my actual trigger to have the current behaviour? If I update only one row works great but if I update multiple rows in the same time I get the error:

Msg 512, Level 16, State 1, Procedure TriggerUpdateAdvert, Line 9
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

This is my trigger:

 ALTER TRIGGER dbo.TriggerUpdateAdvert
 ON dbo.Advert FOR UPDATE
 AS
    DECLARE @OldStatus INT;
    DECLARE @NewStatus INT;
    DECLARE @ID UNIQUEIDENTIFIER;

    SET @ID=(SELECT I.ID_Advert FROM INSERTED I);
    PRINT @ID
    SET @OldStatus=(SELECT D.Status FROM DELETED D WHERE D.ID_Advert=@ID);
    SET @NewStatus=(SELECT I.Status FROM INSERTED I WHERE I.ID_Advert=@ID);


    IF(@OldStatus!=@NewStatus)
    BEGIN
    print @OldStatus
    print @NewStatus
        IF(@NewStatus=1 or @NewStatus=3)
        BEGIN
            UPDATE Advert SET Published_Date=GETDATE() WHERE ID_Advert=@ID
        END
    END


 GO

Edit:

I created next code:

        UPDATE A
        SET A.Published_Date=GETDATE()
        FROM Advert A
        INNER JOIN Inserted I ON A.ID_Advert=I.ID_Advert
        INNER JOIN Deleted D ON D.ID_Advert=A.ID_Advert
        WHERE I.Status!=D.Status  AND (I.Status IN (1,3) AND D.Status NOT IN (1,3))

Do you have any ideas?

Your main flaw is that you assume the trigger will be called once per row - it's NOT! It will be called once per statement and the Inserted pseudo table can contain multiple rows - so in that case - which of those multiple rows are you selecting here??? SET @ID=(SELECT I.ID_Advert FROM INSERTED I); .... you need to rewrite your trigger to take multiple rows into account and handle them appropriately! — marc_s
– marc_s, Commented Nov 26, 2013 at 13:16

Twinkles · Accepted Answer · 2013-11-26 13:21:10Z

2

Avoid using PRINT in a trigger.

And define it like this:

 ALTER TRIGGER dbo.TriggerUpdateAdvert
 ON dbo.Advert FOR UPDATE
 AS
   UPDATE Advert SET Published_Date=GETDATE() 
   WHERE ID_Advert IN (
     SELECT i.id 
     FROM inserted i 
     INNER JOIN deleted d 
     ON i.ID_Advert = d.ID_Advert
     WHERE i.Status <> d.Status
     AND i.Status IN (1,3)
   )
 END

answered Nov 26, 2013 at 13:21

Twinkles

1,9941 gold badge17 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Aaron Bertrand · Accepted Answer · 2013-11-26 13:54:44Z

1

UPDATE a SET Published_Date = CURRENT_TIMESTAMP
  FROM dbo.Advert AS a
  INNER JOIN inserted AS i ON a.ID_Advert = i.ID_Advert
  INNER JOIN deleted AS d  ON i.ID_Advert = d.ID_Advert
  WHERE i.Status IN (1,3) AND d.Status <> i.Status;

answered Nov 26, 2013 at 13:54

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

3 Comments

Aaron Bertrand Over a year ago

@Otix Sorry, for some reason I did not see the edit, I think I was sitting on this question and didn't notice it come in. So you added the edit, is there still a question here? That can't possibly be producing the error message you originally reported, so what other ideas are you looking for here?

POIR Over a year ago

Actually I am looking for the fastest/best way.... If this is the only way it's ok. I will use this method

Aaron Bertrand Over a year ago

I don't think you're going to find any faster approaches.

Collectives™ on Stack Overflow

Handle multiple rows in sql trigger update

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related