quote:

---

Originally posted by TG

Not sure I agree with Fred's point: "...there is no need to check it later when inserting the data...".
It's always good to pre-validate but it's also good practice to constrain the database to support the business rules.

Could be done with either a check constraint or a trigger.

How does your model support when a borrower returns an item to the lender?

Be One with the Optimizer
TG

---

use tempdb
create table b (LenderID int, BorrowerID int, ItemID int)
go

create function dbo.fn_BorrowDifferential(@id int)
returns int
as
begin
	declare @d int
	select	@d = 
		sum(case when borrowerid = 1 then 1 else 0 end)
		-
		sum(case when lenderid = 1 then 1 else 0 end)
	from	b
	where	lenderid = @id or BorrowerID = @id
	return @d
end
go
alter table b add constraint ck_borrowerDifferential check (dbo.fn_BorrowDifferential(borrowerid) <= 5)
go

insert b values( 11, 1, 100)
insert b values( 12, 1, 100)
insert b values( 13, 1, 100)
insert b values( 14, 1, 100)
insert b values( 15, 1, 100)

--check differential
select dbo.fn_BorrowDifferential(1)

--these will fail
insert b values( 16, 1, 100)
insert b values( 17, 1, 100)

--return two items
insert b values( 1, 11, 100)
insert b values( 1, 12, 100)

--check differential again
select dbo.fn_BorrowDifferential(1)

--now these will succeed
insert b values( 16, 1, 100)
insert b values( 17, 1, 100)

select dbo.fn_BorrowDifferential(1)
go

drop table b
drop function dbo.fn_BorrowDifferential