Subquery returned more than 1 value. on MS SQL 2005 trigger.

Question

Hi,

I am not so experienced on databases, so bare with me. ;-) A quite simple FOR INSERT trigger keeps returning the error "Subquery returned more than 1 value.." when inserting more than one row at a time. When i insert rows into the table SOA.dbo.photos_TEST using a statement like;

INSERT INTO SOA.dbo.photos_TEST (id,length,picture,type,name,value,arrivaldatetime)
SELECT  VW.id, ... ,
FROM SOA.dbo.AeosPhotoTEST_VW vw
WHERE ...

The insert fails. But when i add a TOP(1) to the SELECT statement the trigger does not report an error. So probably the "SELECT VALUE FROM INSERTED" statement in the trigger does return all rows in the INSERTED table. Should i iterate in the trigger througg all the rows in INSERTED ? Any suggestion is welcome.

The current code of the trigger is;

SELECT @VALUE = (SELECT VALUE FROM INSERTED)

SET NOCOUNT ON

BEGIN
    DELETE FROM SOA.dbo.photos_TEST 
    WHERE   (value = @VALUE ) 
    AND (arrivaldatetime < (SELECT arrivaldatetime 
                FROM INSERTED
                WHERE value = @VALUE))
END

Answer 1

Modify the T-SQL Trigger deletion logic to the following:

DELETE A
FROM SOA.dbo.photos_TEST A
   INNER JOIN INSERTED B on
       A.VALUE = B.VALUE
WHERE A.arrivaldatetime < B.arrivaldatetime 

Answer 2

I assume you need to remove all old values. Adjusting your trigger as below does the trick.

Trigger

  BEGIN 
    DELETE p
    FROM  SOA.dbo.photos_TEST p
          INNER JOIN INSERTED i ON i.Value = p.Value
                                   AND i.ArrivalDateTime > p.ArrivalDateTime
  END