SQL Triggers calling upon multiple tables

Question

I'm creating a row-level trigger for Oracle using SQL Tools. I will attach a picture of the ERD I am working off of. I will also include my instructions, paste what code I have tried, and the error I am getting.

Description of request: Create a row-level trigger named "TRG_USED_COST" on the USED table. The trigger should execute BEFORE a new row is inserted in the table. The insert command will provide a value for "CHEM_NUM","JOB_NUM", and "USED_QTY" that are being added to the table. Using the CHEM_NUM being used in the insert, retrieve the "CHEM_UNIT_COST" from the CHEMICAL table. Multiply the chemical unit cost times the quantity of chemicals used in this row, and include that value as the "USED_CHARGE" for the row being inserted.

My code:

CREATE OR REPLACE TRIGGER TRG_USED_COST
BEFORE INSERT OR UPDATE OF CHEM_NUM,JOB_NUM,USED_QTY ON USED
FOR EACH ROW
BEGIN
  UPDATE USED
  SET USED_CHARGE = CHEM_UNIT_COST * USED_QTY
  WHERE CHEM_NUM IS NOT NULL;
END;
/

After I enter the code, I receive the error: PL/SQL: ORA-00904: "CHEM_UNIT_COST": invalid identifier

I know that I'm getting the error because I did not reference the CHEMICAL table that "CHEM_UNIT_COST" is located in...but I don't know how to reference it.

http://tinypic.com/r/ipv6fd/8 Link to the picture of the ERD — Sw33tH2O, May 05 '14 at 17:10

score 0 · Answer 1 · answered May 05 '14 at 17:56

You have several issues here. First, you do not "update" the target table in this case. Use the :new attribute on the trigger. Then select the cost from the other table.

CREATE OR REPLACE TRIGGER TRG_USED_COST
BEFORE INSERT OR UPDATE OF CHEM_NUM,JOB_NUM,USED_QTY ON USED
FOR EACH ROW
DECLARE
  v_cost CHEMICAL.CHEM_UNIT_COST%TYPE;
BEGIN
  SELECT CHEM_UNIT_COST
  INTO   v_cost
  FROM   CHEMICAL 
  WHERE  CHEM_NUM = :new.CHEM_NUM;

  :new.USED_CHARGE := v_cost * :new.USED_QTY;

END;
/

SQL Triggers calling upon multiple tables

1 Answers1