Beginning Oracle® Application Express

8.4. Checking against Credit Limit

The observant among you may have taken notice of a field on the CUSTOMERS table called CREDIT_LIMIT. The imaginative among you may have theorized that this value was intended to place an upper cap on the amount of an order for the given customer — and you would be right.

Your final PL/SQL challenge will be to create another validation, which will check to see whether a change to an ORDER_ITEMS row would cause the order to be over the credit limit, and to return an error in the event that it does.

8.4.1. Problem Definition and Solution Design

The problem you wish to address is easily stated — you don't want to let a customer submit an order where the total of the extended prices of each order item line exceed the customer's credit limit.

Since you want to prevent this event from occurring, it makes sense to use a validation to implement this logic. The validation will have to collect some information from related tables, such as the credit limit from the CUSTOMERS table and the current ORDER_TOTAL from the ORDERS table.

Once you have these values, you will have to figure out how much the current extended price adds to the overall total for the order. For a new order item, you can simply add the new extended price to the existing order total. For an updated order item, you will have to calculate the difference between the old extended price and the new extended price and apply that to the current order total. You will not have to run the validation ...