Enforcing business rules in Oracle

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 开发者_JAVA技巧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.

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.

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)

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.