Business rules

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

[Hardy]

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.

[Part Pic]

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.

[Brian Tkatch]

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.

[Bob Stearns]

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.