CODEWITHSUNDEEP
sql-servertriggers
pcvnes
pcvnes

Reputation: 967

Subquery returned more than 1 value. This is not permit error in AFTER INSERT,UPDATE trigger

Once again.. i have the trigger below which has the function to keep/set the value in column esb for maximum 1 row to value 0 (in each row the value cycles from Q->0->R->1) When i insert more than 1 row the trigger fails with an "Subquery returned more than 1 value. This is not permitted when the subquery follows" error on row 38, the "IF ((SELECT esb FROM INSERTED) in ('1','Q'))" statment.

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

any suggestions are 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

Upvotes: 3

Views: 1784

Answers (1)

Joe Stefanelli
Joe Stefanelli

Reputation: 135799

Try to rewrite your IF as:

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

Upvotes: 3

Related Questions