Create Oracle Update Trigger

Question

Hi guys first time asking here in Stack Overflow.

So my question is as follows: When a row is inserted into table A reward points need to be calculated and they must be added to an earned points column in table B. We also need to calculate extra points depending on the tier of the Customers in Table B. Table B has a FK that references Table C which contains the attribute I Need so that my calculation for earned points is correct.

Ive managed to compile the following code which basically links Table A and B but I don't know how to get the value I need from table C in order for this to be correct.

CREATE OR REPLACE TRIGGER JavierRewards
AFTER INSERT ON Purchases
FOR EACH ROW
BEGIN
    UPDATE customers
    SET customers.earned_points = customers.earned_points + Round(:New.purchase_amount * 1.5)
    WHERE customers.cust_id = :new.cust_id;
END;

Any ideas? First time learning triggers so its a bit frustrating.

Answer 1

You might use the following one with an extra select statement :

CREATE OR REPLACE TRIGGER JavierRewards
AFTER INSERT ON Purchases
FOR EACH ROW
    v_Extra_Amount TableC.Extra_Amount%type;
BEGIN
   BEGIN
      SELECT c.Extra_Amount 
        INTO v_Extra_Amount
        FROM TableC c 
        JOIN customers s on s.c_ID = c.ID
       WHERE s.cust_id = :new.cust_id;
     EXCEPTION WHEN no_data_found THEN v_Extra_Amount := 0;
   END;

    UPDATE customers s
       SET s.earned_points = s.earned_points + Round(:New.purchase_amount * 1.5) 
                            + (:New.Purchase_amount * v_Extra_Amount)
     WHERE s.cust_id = :new.cust_id;
END;