Linked Questions

Popular Questions

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

Asked by At

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

Related Questions