The best way to enforce a constraint of SUM([child_table].[value]) <= [parent_table].[total_value]?

Other SQL Server Topics
1

I'm working with a system where a Quote can be created for a fixed amount of money, and items added to it later by other users. The sum of those item amounts should of course never exceed the quote amount.

For example:

CREATE TABLE Quote (
  QuoteId INT IDENTITY PRIMARY KEY,
  QuoteAmount DECIMAL(9,2) NOT NULL
);

CREATE TABLE QuoteItem (
  ItemId INT IDENTITY PRIMARY KEY,
  QuoteId INT NOT NULL,
  ItemAmount DECIMAL(9,2) NOT NULL,
  CONSTRAINT FK_QuoteItem_Quote FOREIGN KEY (QuoteId) REFERENCES Quote(QuoteId),
);

In the above, for each QuoteId, SUM(QuoteItem.ItemAmount) should always be <= Quote.QuoteAmount.

In other words, the following should never return any results:

SELECT q.QuoteId
FROM Quote q
INNER JOIN QuoteItem qi ON q.QuoteId = qi.QuoteId
GROUP BY q.QuoteId, q.QuoteAmount
HAVING SUM(qi.ItemAmount) > q.QuoteAmount;

The only way I know to enforce this as a constraint is to:

a) Create a scalar function that accepts the QuoteId, does the above check and returns 1 for pass, 0 for fail.

b) Add a check constraint on QuoteItem which calls that scalar function, passing the QuoteId and checking that it returns 0.

I realise using a function for a check constraint is bad for performance, as it operates row-by-row. It's a relatively low-activity system, so that might not be a problem, however I'd love to know if there is a better (ed: more performant / best practice) way of achieving the above?

ed2: The application does enforce this at data entry, but it's an important enough rule that a hard constraint on the db is desired as well. And. from what I've read, a trigger won't be as reliable as a constraint in this case(?)

2

I would create a stored procedure for this and only allow data to be updated/inserted/deleted by the stored procedure. If you have done this the users will think: he can do this! Maybe he can do this, this en this also and before you know you have a spagetti code. If you don't want to create a stored procedure then I would use the trigger.

3

Why is the Quote table even necessary? Simply sum it up from QuoteItem. If needed, maybe materialize a view off QuoteItem

4

A constraint makes sense in that situation and is likely the preferred solution.