Subquery returned more than 1 value. This does not resolve the error in AFTER INSERT, UPDATE trigger

Question

Once again .. I have a trigger below which there is a function to save / set the value in the esb column for a maximum of 1 row to a value of 0 (in each row the value of the loop is from Q-> 0-> R-> 1) When I insert more than 1 row, the "Subquery" trigger fails to return more than 1 values. This is not valid when the subquery follows the error on line 38, "IF ((SELECT esb FROM INSERTED) in (" 1 ", 'Q'))" statment.

I understand that "SELECT esb FROM INSERTED" will return all insert rows, but does not know how to process one row at a time. I also tried this by creating a temporary table and iterating through the result set, but subsequently found out that temporary tables based on the INSERTED table are not allowed.

any suggestions welcome (again)

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER TRIGGER [TR_PHOTO_AIU]
   ON          [SOA].[dbo].[photos_TEST]
   AFTER     INSERT,UPDATE
AS 

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

SET @MAXCONC = 1      -- 1 concurrent process

-- 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
                   INNER JOIN INSERTED i ON i.Value = p1.Value
                   AND i.ArrivalDateTime > p1.ArrivalDateTime
                   WHERE  p1.esb = 'Q'
                   ORDER BY p1.arrivaldatetime ASC 

            ) p2

        END
    END
END

Answer 1

Try rewriting your IF as:

IF EXISTS(SELECT 1 FROM INSERTED WHERE esb IN ('1','Q'))
...