I have to create a trigger that applies a 15% discount to any purchase made by the top client (the one with the highest total purchase amount among all the clients). But there are some compilation errors.
CREATE OR REPLACE TRIGGER TOP_DISCOUNT
BEFORE INSERT ON PURCHASE
FOR EACH ROW
DECLARE M_A_X NUMBER;
BEGIN
SELECT MAX(AMOUNT) INTO M_A_X FROM PURCHASE;
SELECT (:NEW.AMOUNT * 0.85) INTO :NEW.AMOUNT FROM DUAL;
WHEN AMOUNT IN M_A_X;
END;
/