November 5, 2009 at 10:19 pm
Hello all,
I believe this can be done, but wanted to make sure. Ok I have two tables. The Dues table contains the member dues owed by each memeber in a non profit organization (This may difffer between members). The Payment Table contains all the payments the members have paid. There is a relationship between these tables. I wanted to make sure there is a constraint on the Payment table that will not allow the sum of the payments for a member in the payment table to be greater that that member's dues owed in the Dues table.
Is there a way to add a constraint like this? If so, how would I go about doing that?
Thanks,
Strick
November 6, 2009 at 6:12 am
Generally one would do this with triggers (though you have to be careful to handle UPDATEs and DELETEs on both tables gracefully too), though if you are happy to lose the greater protection afforded by such logic, you could enforce your business rules through the procedures that modify the tables concerned.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
November 6, 2009 at 6:26 am
As Paul mentioned, you could add this kind of business logic in a procedure, a trigger or even using udf functions as a constraint...but...
wouldn't that logic prevent any member from paying his dues in advance? i would think that is a very typical scenario, not sure you'd want to institute data restrictions like that without thinking it through.
Lowell
November 6, 2009 at 7:52 am
No, a member can pay in advance all the way up to the Dues total for that year. I thought about triggers, but they won't prevent someone from paying too much for a year. I want the dbms to not even allow this to happen. Just like if I try to delete a primary key record that has a foreign key record in another table. The DBMS won't let it happen. I know I can prevent people from paying too much at the front end by making the application check the dues table for that year but I wanted to take the extra step and prevent this from happening at the back end as well.
Thanks,
Strick
November 6, 2009 at 4:41 pm
Maybe you can denormalize the structure a bit, which will make some of the queries simpler as well. You can put sum of payments to the Members table and Update this sum with a trigger on Payments table. Then it is also easy to put a CHECK constraint that will keep the payments below the dues level. I wrote a small testing script, see if this is what you were after:
create table Members
(
MemberId int primary key,
Dues money,
PaymentsSum money,
constraint CHK_DuesPayments check(Dues >= PaymentsSum)
)
go
create table Payments
(
MemberId int not null,
Payment money,
constraint FK_Members foreign key (MemberId) references Members(MemberId)
)
go
create trigger trg_payment on Payments
for insert, update, delete
as
begin
update Members set PaymentsSum = PaymentsSum - b.Payment
from Members a inner join
(select sum(Payment) Payment, MemberId from deleted d group by MemberId) b
on a.MemberId = b.MemberId
update Members set PaymentsSum = PaymentsSum + b.Payment
from Members a inner join
(select sum(Payment) Payment, MemberId from inserted i group by MemberId) b
on a.MemberId = b.MemberId
end
go
insert Members(MemberId, Dues, PaymentsSum) values (1, 10, 0)
insert Members(MemberId, Dues, PaymentsSum) values (2, 10, 0)
insert Members(MemberId, Dues, PaymentsSum) values (3, 10, 0)
go
--testing inserts--
--these two inserts succeed
insert Payments(MemberId, Payment) values (1, 4)
select * from Members where MemberId = 1
insert Payments(MemberId, Payment) values (1, 4)
select * from Members where MemberId = 1
go
--this insert fails because sum of payments is bigger than dues for this member
insert Payments(MemberId, Payment) values (1, 4)
select * from Members where MemberId = 1
go
--testing updates--
--this will succeed
insert Payments(MemberId, Payment) values (2, 10)
select * from Members where MemberId = 2
go
--this will work as well
update Payments set Payment = 5 where MemberId = 2
select * from Members where MemberId = 2
go
--this will not work
update Payments set Payment = 11 where MemberId = 2
select * from Members where MemberId = 2
go
--testing deletes--
--this will succeed
insert Payments(MemberId, Payment) values (3, 3)
insert Payments(MemberId, Payment) values (3, 3)
insert Payments(MemberId, Payment) values (3, 3)
select * from Members where MemberId = 3
delete Payments where MemberId = 3
select * from Members where MemberId = 3
select * from Payments
go
--testing multiple inserts
insert Payments(MemberId, Payment)
select 3, 3 union all
select 3, 3
select * from Members where MemberId = 3
delete Payments where MemberId = 3
select * from Members where MemberId = 3
delete Payments where MemberId = 2
insert Payments(MemberId, Payment)
select 3, 3 union all
select 3, 3 union all
select 2, 3 union all
select 2, 1 union all
select 3, 3
select * from Members where MemberId in(2, 3)
delete Payments where MemberId in (2, 3)
select * from Members where MemberId in (2, 3)
go
--CLEANUP
drop table Payments
drop table Members
Regards
Piotr
...and your only reply is slàinte mhath
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply