Why "Subquery returned more than 1 value. This is not permitted.." for same query in trigger and not as execute normal query

Question

Hi,

I am still trying to get of triggers right, but when is working it seems the other starts (failing) again. Annoying.. but also educational :-)

When i executed the following SQL in MS SQL server is completes successfully, but when it is executed in a AFTER UPDATE trigger it fails with the error

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

SQL;

        UPDATE p2
        SET p2.esb = '0'
        FROM ( SELECT TOP(5) p1.esb 
               FROM SOA.dbo.photos_TEST  p1
               WHERE  p1.esb = 'Q'
               ORDER BY p1.arrivaldatetime ASC 
        ) p2

Why is it not allowed to use a subquery as an expression in a trigger? And is there a workaround ?

thanks again, Peter

Answer 1

Can you try this? Make sure to specify all you your PK columns as equal in the WHERE inside the EXISTS clause.

UPDATE  p1
SET     p1.esb = '0'
FROM    SOA.dbo.photos_TEST p1
WHERE   EXISTS ( SELECT TOP 5
                        *
                 FROM   SOA.dbo.photos_TEST p2
                 WHERE  p1.<KEYFIELD> = p2.<KEYFIELD>
                        AND p2.esb = 'Q'
                 ORDER BY p1.arrivaldatetime ASC )

Answer 2

The solution had to be found in a complete different direction, i accidentaly defined my trigger as a after insert, update trigger instead of a after update trigger. The following trigger definition does work now

CREATE TRIGGER TR_PHOTO_AU
   ON       SOA.dbo.photos_TEST
   AFTER    UPDATE
AS 

DECLARE @MAXCONC INT  -- Maximum concurrent processes
DECLARE @CONC INT     -- Actual concurrent processes

SET @MAXCONC = 1      -- 1 concurrent processes

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON

-- If column esb is involved in the update, does not necessarily mean
-- that the column itself is updated
If ( Update(ESB) )
BEGIN
    -- If column esb has been changed to 1 or Q
    IF ((SELECT esb FROM INSERTED) in ('1','Q'))
    BEGIN
        -- count the number of (imminent) active processes
        SET @CONC = (SELECT COUNT(*) 
                  FROM SOA.dbo.photos_TEST pc
                  WHERE pc.esb in ('0','R'))

        -- if maximum has not been reached
        IF NOT ( @CONC >= @MAXCONC )
        BEGIN
            -- set additional rows esb to '0' to match @MAXCONC
            UPDATE TOP(@MAXCONC-@CONC) p2
            SET p2.esb = '0'
            FROM ( SELECT TOP(@MAXCONC-@CONC) p1.esb 
                   FROM SOA.dbo.photos_TEST  p1
                   WHERE  p1.esb = 'Q'
                   ORDER BY p1.arrivaldatetime ASC 
            ) p2

        END
    END
END