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.

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
> 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.

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!
Bob Stearns wrote:

Part Pic
> 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.

Bob Stearns wrote:
> 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.

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.

Brian Tkatch wrote:
> Bob Stearns wrote:
>
>>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.
>
>
> 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.
>
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.