Question

SQL Triggers: ARE VARELA VSA LINE NTS LINE RPHE DATE DU MEADDE Create a trigger named trg prod QOH_on_line_update that will automatically update the product quantity on hand for a product when a corresponding product LINE row is updated. A template has been created for you below for this trigger. Fill in the ... areas with the proper code: CREATE OR REPLACE TRIGGER trg prod_QOH_on_line_update BEFORE UPDATE ON LINE FOR EACH ROW BEGIN UPDATE SET WHERE END; COMMIT;

Answer 1

The Sql statement for creating a trigger that will automatically update the product quantity on hand for a product when a corresponding product LINE row is updated.

CREATE OR REPLACE TRIGGER trg_prod_QOH_on_line_update
BEFORE UPDATE ON LINE
FOR EACH ROW
BEGIN
   UPDATE PRODUCT
   SET P_QOH = P_QOH + :NEW.LINE_UNITS
   WHERE P_CODE = :NEW.P_CODE;
END;
/
COMMIT;  

The above trigger is fired whenever a row in LINE table is updated and the product's quantity on hand in Product table is updated i.e the LINE_UNITS in LINE table are added to P_QOH field in PRODUCT table for that product.