Im stuck on a the type of trigger needed to for this constraint.
I will have a price and a commission. The price determines the commission amount, < 100 - 4%, < 200 - 5% etc.
My idea.
the database contains a separate table that will hold 4 price values , 101, 201, 401, 601, with their own matching comission %, this will be called PC. When i create a property listing i want to calculate the commission they earn depending on the price entered.
on insert, i need to check the new.price and compare it to the prices in PC. Once new.price is less than the price tuple, i set the price to that commission value
create or replace TRIGGER findCommission BEFORE INSERT OR UPDATE ON HASLISTING
FOR each ROW
BEGIN
IF (:NEW.ASKING_PRICE < 100001) THEN
:NEW.COMMISSION = 6.0;
END IF;
IF (:NEW.ASKING_PRICE < 250001) THEN
:NEW.COMMISSION = 5.5;
END IF;
IF (:NEW.ASKING_PRICE < 1000001) THEN
:NEW.COMMISSION = 5.0;
END IF;
IF (:NEW.ASKING_PRICE > 1000000) THEN
:NEW.COMMISSION = 4.0;
END IF;
END;
