1

I asked a similar question a few months back and I'm improving upon my solution!

I have an Oracle database where I would like to trigger a procedure to do additional work on a column upon updating of another.

For example, say that if I mark an item on sale, I would like the database to automatically take 20% off the price. So:

| ID | ITEM                     | PRICE | ONSALE | DISCOUNT1 | DISCOUNT2 |
|----|--------------------------|-------|--------|-----------|-----------|
| 1  | T-Shirt                  | $20   | F      | .15       | .3        |
| 2  | Sweatshirt               | $30   | F      | .25       | .5        |
| 3  | Jeans                    | $40   | F      | .1        | .4        |
| 4  | Fashionably Ripped Jeans | $120  | F      | .05       | .1        |

What if I wanted the trigger to, say, programmatically select DISCOUNT1 or DISCOUNT2, how would I do this?

:NEW.PRICE := :NEW.PRICE - 
    (:NEW.PRICE * :NEW.DISCOUNT(do tricky math to get a random number));
0

2 Answers 2

1

I was not able to solve this issue in the method I had wanted. I used an if statement as suggested by Serg.

if ((:NEW.ONSALE = 'T') AND (:OLD.ONSALE = 'F')) then
    :NEW.PRICE := :OLD.PRICE - :OLD.PRICE * :NEW.DISCOUNT1;
elsif ((:NEW.ONSALE = 'T') and (:OLD.ONSALE = 'T')) then
    :NEW.PRICE := :OLD.PRICE - :OLD.PRICE * :NEW.DISCOUNT2;
end if;

This was a very simplified example but I couldn't figure out a better way to do this.

0

I think you want to change your table structure. Discounts and items are related but are not always the same. Today you discount T-Shirts by 15% tomorrow you realize you have way too many and want to change that to 20%.

Sure you can update the table but then you loose all records of what the discount was. When a customer calls to say they only got a 15% discount but should have received 20% all you can say is that it is 20% now.

I suggest breaking this into an

  • ITEM table:ID, ITEM, PRICE
  • SALE table ID,SALE_ID, ITEM_ID, QUANTITY, PRICE, DISCOUNT_ID
  • DISCOUNT table: ID, PCT_DISCOUNT -holds all your discount amounts 0%, 5%, 10%, 15% and so on
  • ITEM_DISCOUNT table: ID, ITEM_ID, DISCOUNT_ID, VALID_FROM, VALID_TO,ENABLED

With this structure the ITEM_DISCOUNT table is a poor mans version of a temporal table as it stores all the discounts that ever applied to an item and the time frame that they were applicable in. Instead of using a trigger put your business logic of selecting which discount to apply in a package

To calculate the price for a sale you would use a package

SELECT PCT_DISCOUNT
FROM DISCOUNT JOIN ITEM_DISCOUNT ON DISCOUNT.ID = ITEM_DISCOUNT.DISCOUNT_ID
WHERE ITEM_DISCOUNT.ITEM = v_item
AND ITEM_DISCOUNT.ID = <some business logic>;

vPrice := (PRICE * QUANTITY)* PCT_DISCOUNT;

Although this may look like table churn it allows you to calculate the price and discount that was applied today and in the past. You can change your discount every day by inserting a new row in ITEM_DISCOUNT

1
  • Thanks for the thought @Kevinsky. However, changing the table structure is not something I can do. The table is a part of a larger application.
    – scuba_mike
    May 10, 2016 at 17:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.