检查列值是否高于其他具有相应列的行

Question

在SQL Server中，我在检查约束中检查多行时遇到问题。

对于一个投标系统，有一个包含以下列的表:

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

我想确保插入的出价比该ObjectID的其他出价具有更高的BidAmount。

我尝试在检查约束中使用用户定义的函数来执行此操作，但是结果证明这非常不可靠(有时允许插入，有时拒绝插入)。似乎没有什么规律)。

Bid表:

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))
)

我尝试使用的用户定义函数:

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

我该如何防止系统插入的行BidAmount低于同一产品上的其他数量?

Answer 1

我在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

谢谢你的意见。

Answer 2

CHECK Constraints非常适合对单行进行检查。当试图访问目标表或其他表上的其他行时，情况会变得复杂。

我强烈认为执行这个逻辑并不是表的责任。出价就是这样。你可以让出价A=10美元，随后的出价B=20美元，但随后检测到错误，你实际上必须将出价A更新为30美元。接下来会发生什么呢?可能没有什么;另一种选择是扩展你的问题，包括更新，导致更复杂的情况。

相反，运行INSERTs的用户有责任正确地执行该操作。更有组织的方法是使用过程从用户/应用程序执行插入。该进程可以在插入和throw之前进行检查，而无需使用触发器。