Why does this trigger fail? It says invalid identifier

Question

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

Answer 1

Oracle can see your table structures. We can't. I'm betting Oracle sees the problem in your code.

It should point to the first line with the error. If not, select * from user_errors;

Maybe your ORDERS table doesn't have SHIP_DATE (since the first statement talks about EXPECTED_SHIP_DATE).

Answer 2

Not an Oracle guy, but shouldn't you use

:OLD.SHIP_DATE

or

:NEW.SHIP_DATE

and

:OLD.ORDER_DATE

or

:NEW.ORDER_DATE

refer: Oracle's trigger doc

Answer 3

I'm sorry,Why not create o BEFORE INSERT trigger on orders table? when create BEFORE INSERT trigger,if add a new row in orders table,Can use .NEW.EXPECTED_SHIP_DATE,.NEW.EXPECTED_RECEIVE_DATE update orders table. Don't create materialized view on orders