Update trigger Error

Question

I am trying to perform an update on a table that has the following update trigger but I am getting the following error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Here is the trigger...

If the value of photoRatingID is set to NULL then it deletes records from another table

IF UPDATE(photoRatingID)
BEGIN
    IF (SELECT photoRatingID FROM inserted) IS NULL
        BEGIN
            DELETE mbr_Media_Approval_Primary 
            FROM deleted, mbr_Media_Approval_Primary
            WHERE deleted.mbrID = mbr_Media_Approval_Primary.mbrID
        END
END

All tables contain the unique ID 'mbrID'

Remember that more than one row can be inserted or updated at a time - the trigger needs to do set based operations. — StuartLC, Jun 18 '14 at 15:21
@StuartLC He can use a cursor and do it in procedural based operations. But i agree that set based operations are the better way of doing things :) — Dusan, Jun 18 '14 at 16:16
Yes, you'll find that not many folk like the idea of using [cursors](http://stackoverflow.com/questions/58141/why-is-it-considered-bad-practice-to-use-cursors-in-sql-server) in [Sql Server](http://stackoverflow.com/questions/287445/why-do-people-hate-sql-cursors-so-much), and moreso in a trigger ... — StuartLC, Jun 18 '14 at 16:19

score 1 · Answer 1 · answered Jun 18 '14 at 15:38

1

Your SELECT photoRatingID FROM inserted returns more than one row, as the error message says. Therefore the IS NULL comparison cannot work. You can transform your more or less imperative approach to a set based approach like this:

IF UPDATE(photoRatingID)
BEGIN
   DELETE map
   FROM mbr_Media_Approval_Primary map
       JOIN deleted d ON d.mbrID=map.mbrID
       JOIN inserted i ON i.mbrID=d.mbrID
   WHERE i.photoRatingID IS NULL
END

answered Jun 18 '14 at 15:38

Martin K.

1,050
8
19

Will this perform lookup on every row that is updated (slow performance)? only want to perform lookup when the value is set to NULL – George Filippakos Jun 18 '14 at 15:44
Depends on the data and the update set. If you worry about performance - you will not beat a declarative query with a cursor, a loop or something else. It's the purpose of the DBMS to choose the best possible strategy to fulfill the query. – Martin K. Jun 18 '14 at 16:15
Unfortunately I agree with @Martin K. :) but it would be interesting to see what is the real difference in performance between these two. – Dusan Jun 18 '14 at 16:20

score 0 · Answer 2 · answered Jun 18 '14 at 15:56

For this kind of logic you can use a CURSOR example below:

IF UPDATE(photoRatingID)
BEGIN
    DECLARE @photoRatingID AS int;  
    DECLARE KURSOR_photoRating CURSOR FOR
        SELECT photoRatingID FROM inserted; 
    OPEN KURSOR_photoRating;    
    FETCH NEXT FROM KURSOR_photoRating INTO @photoRatingID  
    WHILE @@FETCH_STATUS = 0
    BEGIN       
        IF (@photoRatingID IS NULL)
        BEGIN            
            DELETE mbr_Media_Approval_Primary 
            FROM deleted, mbr_Media_Approval_Primary
            WHERE deleted.mbrID = mbr_Media_Approval_Primary.mbrID          
        END
        FETCH NEXT FROM KURSOR_photoRating INTO @photoRatingID
    END 
    CLOSE KURSOR_photoRating;    
    DEALLOCATE KURSOR_photoRating;
END

Update trigger Error

2 Answers2