title: Oracle Sql trigger before update of column
body: <p>I want to create an update trigger for a table called purchases that will update the total cost field and also log a record in the purchases log table to indicate which purchase was updated.</p>
  
  <p>I don't have much experience with creating triggers but so far this is what i've got</p>
  
  <pre><code> CREATE OR REPLACE TRIGGER Update_Purchase_Audit BEFORE
  UPDATE ON Purchases
   REFERENCING NEW AS NEW OLD AS OLD
  FOR EACH ROW
  WHEN(NEW.p_id&gt;0)
  DECLARE total_cost INTEGER;
   BEGIN
     set total_cost := (:NEW.quantity * :NEW.unitcost);
     :NEW.total_cost:=total_cost*0.165;
  
    INSERT INTO Purchase_Log(eventdate, p_id, descrip)
     VALUES(SYSDATE, :NEW.p_id, :NEW.product);
  END;
  /
  </code></pre>
  
  <p>I get this error when creating the trigger:
  trigger created with compilation errors. When I run show errors; I get ORA-00922: missing or invalid option</p>
  
  <p>Any suggestion? Thanks</p>