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

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) 开发者_如何学Pythonto 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

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 

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