CREATE MATERIALIZED VIEW ORDERS_MV
BUILD IMMEDIATE
REFRESH COMPLETE ON DEMAND AS
SELECT * FROM ORDERS;
---------------------------------------

CREATE OR REPLACE TRIGGER update_ship_receive
INSTEAD OF INSERT ON ORDERS_MV
FOR EACH ROW
BEGIN
  UPDATE ORDERS SET EXPECTED_SHIP_DATE = ORDER_DATE+5;
  UPDATE ORDERS SET EXPECTED_RECEIVE_DATE = SHIP_DATE+1 
WHERE SHIPPING_METHOD = '1 DAY';
  UPDATE ORDERS SET EXPECTED_RECEIVE_DATE = SHIP_DATE+2
 WHERE SHIPPING_METHOD = '2 DAY';
  UPDATE ORDERS SET EXPECTED_RECEIVE_DATE = SHIP_DATE+5 
WHERE SHIPPING_METHOD = 'GROUND';
END;
/