How to solve "Warning: triggers are created with compilation errors"?

yyyyy 0
Viewed 23

Hi sorry for the inconvenience of posting a bad question. So below is the edit version of the question:

Purpose of this task: In this task, I am trying to apply the company’s top client is the one who has purchased the most a discount with 15% discount. One of the requirement before creating this trigger is that - should not hardcode the top client since the top client could change when more purchases are made by other clients. I have created a trigger called TOP_CLIENT and edited the code as below:

AFTER UPDATE ON PURCHASE
BEGIN 
  SELECT PURCHASENO FROM PURCHASE
  WHERE (SELECT MAX(AMOUNT) FROM PURCHASE P
         JOIN CLIENT C ON P.PURCHASENO = C.CLIENTNO);
UPDATE PURCHASE
SET AMOUNT = (AMOUNT * 0.85)
END;
/

NOTE THAT: The table CLIENT and PURCHASE are already created and existed in the database.

Shown errors within this code: enter image description here

Please comment if any of the above doesn't make sense or have any questions related! Thank you!

oracle
1 Answers

First, I suggest adding a numeric DiscountRate column to the Client discount and populating it the applicable rate for any clients who get a discount.

Next, I suggest adding a numeric column called the same DiscountRate to the Purchase table.

With those in place, you can update any purchase row AFTER INSERT OR UPDATE only if the DiscountRate has not yet been applied to the purchase or is not equal to the current client discountrate, depending on your business logic;

DROP TRIGGER TOP_DOSCOUNT;
/
CREATE TRIGGER TOP_DOSCOUNT
    AFTER INSERT OR UPDATE ON PURCHASE
    FOR EACH ROW
    WHEN DiscountRate IS NULL

    DECLARE dr PURCHASE.DiscountRate%type;
 
    SELECT nvl(C.DiscountRate,0.0) INTO dr FROM CLIENT C
      WHERE PurchaseNo = C.ClientNo;
    UPDATE PURCHASE
    SET Amount = Amount * (1 - dr), DiscountRate = dr;
    END;
    /

I am unsure if you want to constantly be updating all your prior purchase records with new discount rates. If not, then I suggest this should Only be an insert trigger...but only you know your business logic to be implemented!

JasonInVegas0
Related