CODEWITHSUNDEEP
sqloracle-databaseoracle11gtriggers
user2127184
user2127184

Reputation: 131

Oracle Sql trigger before update of column

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.

I don't have much experience with creating triggers but so far this is what i've got

 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>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;
/

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

Any suggestion? Thanks

Upvotes: 0

Views: 3439

Answers (1)

T.S.
T.S.

Reputation: 19330

CREATE OR REPLACE TRIGGER Update_Purchase_Audit 
    BEFORE UPDATE ON Purchases
    FOR EACH ROW
    WHEN (NEW.p_id > 0)
DECLARE 
    total_cost number;
BEGIN
    total_cost := :NEW.quantity * :NEW.unitcost;
    :NEW.total_cost := total_cost * 0.165; --<< this is ok BEFORE update

    -->> Do you have table relations here? THis may need to go AFTER update
    -- in separate trigger
    INSERT INTO Purchase_Log (eventdate, p_id,      descrip)
                      VALUES(SYSDATE,   :NEW.p_id, :NEW.product);

END;
/

Upvotes: 1

Related Questions