HowTo - Constraint for Multi Rows with Same Group ?
-
Hello to all helpers,
I have a table in database:
Create table TestTable (GroupId int, ANumber int)
with samle data :
GroupId ANumber
-------------------
1 10
1 70
1 20
2 60
2 40
3 35
3 40
3 15
3 10
My Buisness Rule requirement is : Keep Group Total to 100
like for
Group 1 >> 10+70+20=100
Group 2 >> 60+40=100
Group 3 >> 35+40+15+10=100
Is it possible to use some constraint ( but it is single row level) ?
How to implement this buisness rule on SQLServer ?
Thanks
-
You could use a trigger that rolls back any inserts that would break your business rule.
-
Maybe this?
create function dbo.grpsum(@GroupId int)returns int
as
begin
return (select sum(ANumber) from dbo.TestTable where GroupId=@GroupId)
end
GO
alter table dbo.TestTable add constraint chkgrp check (dbo.grpsum(GroupId) <= 100)
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Thanks JOWL and MARKS,
JOWL's solution
I think Instead of Trigger will be used.
I have seen Microsfoft's Account Express softare's database. There is not single
Trigger is used. Is microsoft not favour Triggers ?
Marks Solution
Will the performance is an issue with check constraint with function ?
-
There are many databases without triggers!
From MSDN: "DML triggers are frequently used for enforcing business rules and data integrity."
http://msdn.microsoft.com/en-us/library/ms189799(SQL.90).aspx
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply