Business rules

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bob Stearns - 04 Jul 2006 03:24 GMT

For a given invoice/receipt I have the line items sold and possibly
multiple receipt lines: cash, check and credit card(s). The business
rule I wish to enforce is that the sum of the amounts of line items is
equal to the sum of the amounts of receipts. Where and how is such a
rule coded? CHECK(invoice_status<>'PAID' || select sum=select sum)? A
trigger on updating invoice which does a similar thing? Best practice
references would be welcome as well as anything you might have come up with.

Reply to this Message

Hardy - 04 Jul 2006 16:36 GMT

ur table description? then there must be someone to help you.

Bob Stearns

> For a given invoice/receipt I have the line items sold and possibly
> multiple receipt lines: cash, check and credit card(s). The business
[quoted text clipped - 3 lines]
> trigger on updating invoice which does a similar thing? Best practice
> references would be welcome as well as anything you might have come up with.

Reply to this Message

Part Pic - 05 Jul 2006 07:49 GMT

if you are interested in the computer,you can come to my
picture
www.flickr.com/photos/partpic/.
We are the professional photograph website about the
computer.
there are o lot of picture of some kinds of computer
,include
BIM,SUN,HP,EMC,HDS and others.Welcome you!

Part Pic

Reply to this Message

Brian Tkatch - 05 Jul 2006 15:21 GMT

This can be done with an ON INSERT TRIGGER, that checks new.amount
before allowing the change, and RAISEs an EXCEPTION if it is not. It
can also be done via a third TABLE, in which the sums of both are kept
(also with ON INSERT TRIGGERs) and a CONSTRAINT can be used there.

But, i'd like to add that this does not sound like a business rule.
This sounds like a check to be done in the application to review what
happened after a full transaction. Because whether the receipts add up
to the correct amount or not has nothing to do with if it was actually
received. Thus, it is a not a data related rule, rather, it is an
interface rule, which is best put in the application. Or, perhaps, a
PROCEDURE could be used to do the actual INSERT and just bar TABLE
access.

B.

Reply to this Message

Bob Stearns - 05 Jul 2006 16:17 GMT

>>For a given invoice/receipt I have the line items sold and possibly
>>multiple receipt lines: cash, check and credit card(s). The business
[quoted text clipped - 19 lines]
>
> B.

Thanks for the reply.

Each of the receipts may be separated from the others by some time,
especially if there is a problem, so each receipt is a transaction, then
there is a "invoice complete" transaction. It is this last transaction
when I wish to enforce the rule. This is all complicated by being a WEB
based system, which limits my being able to lock things.

Reply to this Message