Another Trigger Problem

[mnewsrx]

I am new to Oracle and continue to have problems creating triggers. I am using the OE schema and Oracle product_information table to get experience with triggers. I want to create a trigger on the Product_Information table that fires on the update of the table. The trigger should not allow a new list price be less than minimum price. Depending on product status price can be reduced
under development for no more than 15%
planned no more than 10%
obsolete can't be reduced at all
orderable no more than 5%
This trigger allows any price increase.

I've tried a couple of ways of doing this with no luck. Are there any suggestions on the approach I should use? Thanks so much.

 

[SantaMufasa]

MNews,

What trigger code have you created so far that we can respond to specifically?

And what have been the specific "problems creating triggers" that you would like to ask us about (so that we don't go about teaching an entire class on triggers here <grin>).

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[LKBrwnDBA]

You could add something like this to your trigger:

CREATE TRIGGER ...Etc...

v_pct=(1 - (:new.list_price/:old.list_price));

IF :new.list_price >= :new.minimum_price
AND ((:new.status  = 'under development' AND v_pct >= 0.85)
 OR  (:new.status  = 'planned' AND v_pct >= 0.90 )
 OR  (:new.status  = 'obsolete' AND v_pct = 0.00)
 OR  (:new.status  = 'orderable' AND v_pct >= 0.90)) 
THEN
  -- All OK...
ELSE
  Raise_application_error(-20001,'Invalid price change.');
END IF;

Perhaps you could add the "list_price >= minimum_price" to the table as a CHECK constraint.


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[Thargy]

MN,

also be advised that triggers are a poor way to implement what you want. If you can state what rules you want to implement, I would be pleased to see if I can deliver declarative constraints for you, that would do the same, and always work.

Triggers are easy to abuse, and only the most experienced of folks get them right. Look for articles by Toon Koppelaars for information on how to do them properly. It is much tougher than you think.

The best solution is usually to write a stored procedure instead.

Regards

T

 

[LKBrwnDBA]

Ooops, some typos:

CREATE TRIGGER ...Etc...

v_pct=(1 - (:new.list_price/:old.list_price));

IF :new.list_price >= :new.minimum_price
AND ((:new.status  = 'under development' AND v_pct >= 0.85)
 OR  (:new.status  = 'planned'   AND v_pct >= 0.90)
 OR  (:new.status  = 'obsolete'  AND v_pct  = 1.00)
 OR  (:new.status  = 'orderable' AND v_pct >= 0.95))
THEN
  -- All OK...
ELSE
  Raise_application_error(-20001,'Invalid price change.');
END IF;

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb