CODEWITHSUNDEEP
sql-server
Josh
Josh

Reputation: 33

Issue with a Trigger in SQL

I'm looking for a trigger which automatically inserts the highest price from table bids into the table opjects at the moment the bit 'closed?' turns from 0 to 1.

I've got the table Objects which has the values(Objectnumber, endprice (null), closed? (bit)(not null, default 0)).

The table bids with the value (Objectnumber,euro(prive of the bid),username)

This is what i currently got,

CREATE TRIGGER AF5 ON objects
FOR INSERT, UPDATE
AS
BEGIN
DECLARE @objectnumber  numeric(10)
DECLARE @endprice       numeric(7,2)
DECLARE @closed?        bit
SET @objectnumber  = (SELECT objectnumber from inserted)
SET @endprice =(SELECT endprice from inserted)
SET @closed? =(SELECT closed? from inserted)
IF EXISTS     (     
                    SELECT subjectnumber
                    FROM  inserted
                    WHERE closed? = 1 AND endprice = NULL           
         )
BEGIN
    PRINT ('Found')
            update objects set endprice = ( 
                    SELECT MAX(b.euro) 
                    from bids b INNER JOIN objects v 
                    on @objectnumber = b.objectnumber  
                    where @closed? = 1  
            )
        END
    ELSE
        BEGIN
            RAISERROR ('Error', 1, 1)
            ROLLBACK
        END
    END
    GO

The error i keep getting is Msg 512, Level 16, State 1, Procedure AF5, Line 8 [Batch Start Line 3834] Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Upvotes: 3

Views: 73

Answers (1)

Mikhail Lobanov
Mikhail Lobanov

Reputation: 3026

inserted table can contains mutiply rows.

I delete your if condition because after update inside IF trigger can be fired again. If you want, check @@ROWCOUNT variable to get count of updated rows and compare it with count of rows in inserted table

CREATE TRIGGER AF5 ON objects
FOR INSERT, UPDATE
AS
BEGIN
    IF @@ROWCOUNT = 0 RETURN

    UPDATE O 
    SET endprice = B.EndPrice 
    FROM Objects        O
    INNER JOIN inserted I on O.objectnumber  = I.objectnumber 
    CROSS APPLY (
        SELECT EndPrice = MAX(B.euro) 
        FROM bids B
        WHERE B.objectnumber = O.objectnumber
    ) B
    WHERE I.[Closed?] = 1 AND I.endprice IS NULL AND B.EndPrice IS NOT NULL
END

Upvotes: 1

Related Questions