Enforcing business rules in Oracle

Question

I have a table called Book. This table has 3 columns viz id, price and discount. If price is greater than 200 then discount should be 20%. While inserting data in Book table the discount value should be updated based on price value. How can this be handled when data is inserted or updated in Book table?

Please provide all possible solutions. I don't want to execute a stored procedure. Let us suppose when user is inserting/updatng data in Book table so he does not execute a function or procedure.

Please provide solution.

Answer 1

If you don't want to use a stored procedure then the only other option is a trigger.

create or replace trigger book_discount_rule
    before insert, update on BOOK
    for each row
begin
    if :new.price > 200 
    then
       :new.discount := 20;
    else
       :new.discount := 0;            
    end if;
end;

Personally I dislike this solution, precisely because triggers are invisible. That is, if the user runs this insert statement ...

insert into book 
     values (book_id_seq.nextval, 250, 30)
/

... they may be puzzled why the stored DISCOUNT is different from the value they submitted. I would rather use a stored procedure to enforce business rules.

Either way, in real life I would prefer to have the rules implemented through an API rather than hard-coding the values. But that is a matter of taste.

---

As Jeffrey points out it is a good idea to back up the trigger (or procedure) with a check constraint on the table to guarantee that the DISCOUNT is appropriate to the price.

alter table book 
    add constraint book_discount_ck 
    check ((price > 200 and discount = 20) or discount = 0)
/

Applying a constraint without either a stored procedure or o trigger requires the user to know the business rule. Unfortunately Oracle does not provide a mechanism to attach a specific error message to our check constraint. The ability to raise a context specific exception with a meaningful message is one of the advantages of stored procedures.

Answer 2

Without using any stored procedures:

ALTER TABLE "Book" ADD (
  CONSTRAINT discount_check
  CHECK (price < 200 OR discount = 0.2)
);

This way, no-one will be able to insert or update a Book unless they insert appropriate values for price and discount.*

*(to be bulletproof, you'd add NOT NULL constraints to these columns as well)

Answer 3

If the discount is always simply a function of the price, then I would suggest making it a calculated column in a view. Given a table Books with columns id and price, create a view like this:

CREATE VIEW books_view AS (
  SELECT
    id,
    price,
    CASE WHEN price > 200 THEN 0.20 ELSE 0 END discount
  FROM books
  );

This way a user cannot set the discount to an incorrect value. With the trigger solutions, the discount may be set correctly on insert but then could be changed by later updates.