Views That Are Not Modifiable:

A view cannot be modified through UPDATE, INSERT, or DELETE statements if the view query holds any of the subsequent constructs:

• Set operators
• Group functions
• GROUP BY, CONNECT BY, or START WITH clauses
• DISTINCT operator
• Joins (a subset of join views are updatable)

If a view holds pseudocolumns or expressions, you can only update the view with an UPDATE statement which does not refer to any of the pseudocolumns or expressions.

Example of an INSTEAD OF Trigger

The  subsequent  example  denotes  an  INSTEAD  OF  trigger  for  inserting  rows  into  the MANAGER_INFO view.

CREATE OR REPLACE VIEW manager_info AS

SELECT e.empno, e.ename, d.deptno, d.dname# for select employee no and name and department name and no.#

FROM emp e, dept d

WHERE          e.deptno =       d.deptno

CREATE OR REPLACE TRIGGER MANAGERINFO_INSERT

INSTEAD OF INSERT ON MAN_INFO FOR EACH ROW

BEGIN

END;

INSERT INTO emp(EMPNO,ENAME) VALUES(:new.empno, :new.Ename);

INSERT INTO dept(DEPTNO,DNAME) VALUES(:new.deptno, :new.dname);

Here, alternate of executing the statement for the view and the body of the view is executed thus inserting records onto the respective tables.