Insert row when only two tables diff of count and sum is

Let's say there are two tables A, B

A
productId, owner
1000, null
1000, null
1000, null
1000, 'john'

B
productId, owner, reserved
1000, 'tom', 1

I'm looking for a way how to insert row to B only when there is enough rows to reserve it.

ex) currently there are only 2 products available for productId=1000 because there are 3 null owners and 1 is reserved in B table. At this moment, If a user try to reserve 3(insert with values 1000, 'bill', 3) It should be failed.

CodePudding user response：

Count how many are available and if greater than 0 allow the insert? I think you've oversimplifed the problem though, you don't mention how the data arrives for insert, and you don't mention what should happen if the insert fails.

declare @productId int = 1000, @owner varchar(128) = 'bill', @reserved int = 3;

insert into B (productId, [owner], reserved)
select @productId, @owner, @reserved
where (select count(*) from A where productId = @productId and [owner] is null)
- (select sum(reserved) from B where productId = @productId) > 0;