Trigger problem in SQL
I'm looking for a trigger that automatically inserts the highest price from table rates into the opjects table at the moment the bit is "closed"? rotates from 0 to 1.
I have an Objects table that has values (Objectnumber, endprice (null), closed? (Bit) (not null, default 0)).
Bid table with value (Objectnumber, euro (prive of the bid), username)
This is what I 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 [start line 3834] Subquery returned more than 1 value. This is not valid when the subquery follows = ,! =, <, <=,>,> = or when a subquery is used as an expression.
source to share
inserted
the table can contain mutiply rows.
I remove your if condition because after updating inside the IF
trigger can be run again. If you like, check the variable @@ROWCOUNT
to get the number of updated rows and compare it with the number of rows in the tableinserted
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
source to share