Check if column value is higher than other rows with corresponding columns-CodePudding

In SQL Server, I'm having problems checking multiple rows in a check constraint.

For a bidding system, there is a table with these columns:

ObjectID [INT] 
BidAmount [NUMERIC(9,2)] 
User [VARCHAR(40)] 
Date [DATETIME]

I want to make sure that an inserted bid has a higher BidAmount than the other bids for this ObjectID.

I tried doing this with a user-defined function in the check-constraint, however this turned out to be very unreliable (sometimes an insert was allowed and sometimes it was denied. There didn't seem to be a pattern).

The Bid table:

CREATE TABLE Bid 
(
    ObjectID INT NOT NULL,
    BidAmount NUMERIC(9,2) NOT NULL,
    User VARCHAR(40) NOT NULL,
    Date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,

    CONSTRAINT PK_Bod 
        PRIMARY KEY (ObjectID, BidAmount),
    CONSTRAINT FK_Bod_User 
        FOREIGN KEY (User) REFERENCES User (Username),
    CONSTRAINT FK_Bod_Object 
        FOREIGN KEY (ObjectID) REFERENCES Object (ObjectID),
    CONSTRAINT AK_Bod_Gebruikersmoment UNIQUE (User, Date),
    CONSTRAINT AK_Bod_Voorwerpmoment UNIQUE (ObjectID, Date),
    CONSTRAINT CK_Bodhoogte 
        CHECK (BidAmount > dbo.krijgMinimaleBod(ObjectID, Date))
)

The user-defined function I tried to use:

CREATE FUNCTION dbo.krijgMinimaleBod (@ObjectID INT)
RETURNS NUMERIC(9,2)
AS
BEGIN
    DECLARE @highestBid NUMERIC(9,2)

    SELECT @highestBid = ISNULL(MAX(BidAmount), V.StartPrice)
    FROM Object V
    LEFT JOIN Bid B ON B.ObjectID = V.ObjectID
    WHERE V.ObjectID = @ObjectID
    GROUP BY V.ObjectID, V.StartPrice;

    RETURN @highestBid
END

How would I go about preventing the system from having rows inserted with a BidAmount that's lower than the other amounts on the same product?

CodePudding user response：

CHECK Constraints are excellent for checks made on a single row. Things get complex when trying to access other rows, on the target table or others.

I strongly feel that enforcing this logic is not really a responsibility of the table. The bids are what they are. You could have bid A=10$, a subsequent bid B=20$, but then an error was detected and you actually have to update bid A to 30$. What should happen then? Probably nothing; the alternative would be to extend your question to include updates, leading to even more complex situtations.

Instead, it is the responsibility of the user that runs INSERTs to do that correctly. The more organized way is to use a procedure to perform insertions from the user/application. This proc can have a check prior to insert, and throw without using a trigger.

CodePudding user response：

I solved this issue by using a trigger in T-SQL.

CREATE TRIGGER Bid_HighEnough ON dbo.Bod
AFTER INSERT, UPDATE
AS
    IF EXISTS (SELECT 'Wrong'
                FROM Inserted I INNER JOIN Bid B
                ON I.ObjectID = B.ObjectID
                INNER JOIN Object V
                ON I.ObjectID = V.ObjectID
                WHERE I.BidAmount < V.StartPrice OR I.BidAmount < B.BidAmount
    BEGIN
        THROW 50000, 'No bids allowed lower than previous bids.', 1
    END

Thank you for your comments.