INSTEAD OF TRIGGER TO MODIFY VIEW in PLSQL

INSTEAD OF TRIGGER is used to perform DML Operations on Views. Normally we cannot perform DML Operations on views.

Let us look in detail:
Create 2 Tables which consists id,name and place
(To copy the code select ctrl+C)
CREATE TABLE test_table (id NUMBER,name VARCHAR2(10)); INSERT INTO test_table VALUES(1,'RG'); INSERT INTO test_table VALUES(2,'PG'); INSERT INTO test_table VALUES(3,'KG'); COMMIT; CREATE TABLE test_table1 (id NUMBER,place VARCHAR2(10)); INSERT INTO test_table1 VALUES(1,'PUNE'); INSERT INTO test_table1 VALUES(2,'SDP'); INSERT INTO test_table1 VALUES(3,'MYS'); COMMIT;

Create a view that is based on above 2 tables
CREATE OR REPLACE VIEW instead_trig_test_v AS SELECT a.id,a.name,b.place FROM test_table a,test_table1 b WHERE a.id=b.id; SELECT * FROM instead_trig_test_v;

Try to insert data to the view instead_trig_test_v.
INSERT INTO instead_trig_test_v values (4,'GK','SDP');

It will not allow to insert data to the view and gives below error

You can insert data to the view by creating INSTEAD OF Trigger.
CREATE OR REPLACE TRIGGER instead_trig_test INSTEAD OF INSERT ON instead_trig_test_v FOR EACH ROW BEGIN -- insert into test_table first INSERT INTO test_table VALUES(:NEW.id, :NEW.name); -- insert a row into test_table1 INSERT INTO test_table1 VALUES(:NEW.id, :NEW.place); END;

Insert a row to the view
INSERT INTO instead_trig_test_v VALUES (4,'GK','SDP');  

The row will be inserted successfully to the view.
You can use the same for UPDATE and DELETE

For INSTEAD OF INSERT triggers to work on views, there are certain restrictions:

The view must not contain any of the following constructs:
1) A set operator
2) A DISTINCT operator
3) An aggregate or analytic function
4) A GROUP BY, ORDER BY, MODEL, CONNECT BY, or START WITH clause
5) A collection expression in a SELECT list
6) A subquery in a SELECT list
7) A subquery designated WITH READ ONLY
8) Joins, with some exceptions.
For more details refer Oracle Database Administrator's Guide