March 12, 2003 at 2:57 pm
I need a little help on a percentage storage validation design strategy.
Assuming:
1) A root table containing "Group" names
2) A Percentage table containing the ID of a Group, a Person and a Percentage
How can I test to make sure that all percentage rows per Group add to 100% ?
Example1:
Group User Percent
Group1 Bob 50
Group1 Joe 50
Group2 Jane 100
etc...
When editing the data - "After" triggers will disallow the "temporary" editing required to make changes.
Any suggestions ????
- B
March 12, 2003 at 3:36 pm
Why not group by on your group ID, and sum the percentages, allowing you to check for 100 with a where clause? Say something like:
Select GroupID,Sum(Percentage)
From PercentageTable
Group BY GroupID
Having SUM(Percentage) <> 100
March 13, 2003 at 6:48 am
Well, thanks and here's why.
If I wanted to change this group from
Group1 Bob 50
Group1 Joe 50
To This:
Group1 Bob 60
Group1 Joe 40
It would probably be accomplished in 2 Update statements.
Update Groups set Percentage = 60 Where Person = Bob
and then
Update Groups set Percentage = 40 Where Person = Joe
But - if an "after trigger" is fired after the first statement to check the totals - the sum would add to 110 (Bob is 60 but Joe is still 50)
It's as if a trigger will not suffice due to the timing of things - or - I need to wrap ALL edits per group into one statement somehow.
The interface can certainly check for 100 and/or a Stored Procedure - but a Trigger ???????
- B
March 13, 2003 at 6:05 pm
The second option you state is how it should be done if that is the case.
Use
Begin Tran
Update Groups set Percentage = 60 Where Person = Bob
Update Groups set Percentage = 40 Where Person = Joe
do your check for 100% here using the grouping to check it
if it doesn't equal 100%
rollback tran
if it does equal 100%
commit tran
And although this is a very simplified version, and I'm sure your situation is more complex considering the requirements you state, I'm sure you could work out the logic of checking the groupings based on the update of a particular member of the group. It could very well be that you adjust the other persons in the group based on the first edit so that by default they get the percentage left, in order to force 100% at any given time. Of course, this gets more complex for every person involved in the group. If there are only two persons per group, by default the remaining person gets the difference. In your example, by setting bob to 60, the after trigger sets joe to 40, since that's required to meet your 100%. There would need to be logic in place to adjust the remining persons for more than two people. Is the grouping able to have more than two people (seems like it would by your example though I can see that business logic could restrict this...)? What is the maximum number of people that can be in a group? Perhaps, a proc for the updates, passing multiple values for name, percentage, name , percentage, etc... wrapped in a transaction would work, and then do not update the data except through the proc, keeping your validation trigger happy.....
Out of curiosity, why try to go with a trigger to start with? It seems like a pre-validation of the values inserted would be a better route anyway, considering the complexity you could get into, unless you allow direct modification of the table through alternate means than your app. If multiple changes are required in order to affect a change to the group, a trigger is NOT going to do it, if you allow single updates to your table. You would have to go one way or the other. Wrapping the multiple updates in a transaction would work fine, until someone tried to use a different method, which logically shouldn't be allowed by the trigger if you intend to maintain the integrity by that means. In other words, something has to give. Either use transactions to do the updates, or don't use triggers, or determine logic to allow single changes to change the other rows, etc...
Edited by - scorpion_66 on 03/13/2003 6:29:58 PM
March 14, 2003 at 6:48 am
Thanks again S -
"Out of curiosity, why try to go with a trigger to start with?"
1) I'm primarily a middle-tier / front-end developer. As such, I've read much of the Microsoft "object" propaganda. However, in my experience, the database is STILL the best place for integrity checking. And, the closer to the data table itself, the better.
Example - If I put such integrity checking into a SProc - good - middle tiers can change with the wind. However, myself, or another DBA can easily bypass or forget to use the SPRoc and edit the percentage table directly and NO checking would occur - errors !!!!!!
For the moment, I have one and only one Mission Critical SProc which uses the percentage table. I added a percentage check at the beginning of that SProc to make sure nothing bad happens 🙂
But still, Relational databases seem to handle what appears to be complex problems in a simple, straightforward way. I can't get over the fact that I cannot come up with a "simple / standard" way to check percentages with Triggers.
- B
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply