Possible Duplicate:
Why does this trigger fail? It says invalid identifier.
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;
/
A materialized view isn't a regular view. It has a data segment related to it.
This doesn't work because a materialized view is not a view it is a special kind of table: it has data, we can enforce constraints with it, etc.
So having got that out of the way let's look at your underlying process logic, which is confusing. You have a materialized view based on a table. Now you want to build a trigger which updates the base table whenever a row is inserted into the materialized view.
Do you expect the materialized view to contain the changed data?
Because of the way you have specified the SQL the trigger (if it could work) would update every row in the ORDERS table.
Because the trigger is FOR EACH ROW refreshing the materialized view would update the entire ORDERS table multiple times, once for each row in the ORDERS table.
INSTEAD OF triggers execute the code in the trigger body rather than (instead of) the action specified in the trigger header. So (if it could work) the trigger would update the ORDERS table and insert no rows into the materialized view.
So I hope you can see that this error is preventing you from making a more serious architectural error. What you need to do is clarify your business process and then seek to express that in SQL. To my mind, the most appropriate solution would be a BEFORE UPDATE trigger on the ORDERS table. Something like this:
You then just have a simple materialized view with no associated processing to undertake when you refresh it. Obviously your actual business logic may dictate a different solution.