Performing DML operation in join views(INSERT)

How to insert a join view ?

You may need to set up the following data structures for this example to work:

CREATE TABLE Project_tab (
   Prj_level NUMBER,
   Projno    NUMBER,
   Resp_dept NUMBER);
CREATE TABLE Emp_tab (
   Empno     NUMBER NOT NULL,
   Ename     VARCHAR2(10),
   Job       VARCHAR2(9),
   Mgr       NUMBER(4),
   Hiredate  DATE,
   Sal       NUMBER(7,2),
   Comm      NUMBER(7,2),
   Deptno    NUMBER(2) NOT NULL);

CREATE TABLE Dept_tab (
   Deptno    NUMBER(2) NOT NULL,
   Dname     VARCHAR2(14),
   Loc       VARCHAR2(13),
   Mgr_no    NUMBER,
   Dept_type NUMBER);

---

The following example shows an INSTEAD OF trigger for inserting rows into the MANAGER_INFO view.

CREATE OR REPLACE VIEW manager_info AS
SELECT e.ename, e.empno, d.dept_type, d.deptno,
p.prj_level,p.projno
FROM   Emp_tab e, Dept_tab d, Project_tab p
 WHERE E.DEPTNO= D.DEPTNo
AND D.DEPT_TYPE=P.PROJNO;

View created.

CREATE OR REPLACE TRIGGER manager_info_insert
INSTEAD OF INSERT ON manager_info
REFERENCING NEW AS n  -- new manager information
FOR EACH ROW
DECLARE
   rowcnt number;
BEGIN
   SELECT COUNT(*) INTO rowcnt
  FROM Emp_tab
  WHERE empno = :n.empno;
   IF rowcnt = 0  THEN
       INSERT INTO Emp_tab (empno,ename)
       VALUES (:n.empno, :n.ename);
   ELSE
      UPDATE Emp_tab
    SET Emp_tab.ename = :n.ename
     WHERE Emp_tab.empno = :n.empno;
   END IF;
   SELECT COUNT(*) INTO rowcnt
  FROM Dept_tab
  WHERE deptno = :n.deptno;
   IF rowcnt = 0 THEN
      INSERT INTO Dept_tab
     (deptno, dept_type)
       VALUES(:n.deptno, :n.dept_type);
   ELSE
      UPDATE Dept_tab
     SET Dept_tab.dept_type = :n.dept_type
      WHERE Dept_tab.deptno = :n.deptno;
   END IF;
   SELECT COUNT(*) INTO rowcnt
  FROM Project_tab
   WHERE Project_tab.projno = :n.projno;
   IF rowcnt = 0 THEN
      INSERT INTO Project_tab
         (projno, prj_level)
         VALUES(:n.projno, :n.prj_level);
   ELSE
       UPDATE Project_tab
  SET Project_tab.prj_level = :n.prj_level
  WHERE Project_tab.projno = :n.projno;
END IF;
END;
/

 Trigger created.

SQL> select * from user_updatable_columns
2  where table_name='MANAGER_INFO';

OWNER TABLE_NAME      COLUMN_NAME     UPD INS DEL
----- --------------- --------------- --- --- ---
HR    MANAGER_INFO    ENAME           NO  YES NO
HR    MANAGER_INFO    EMPNO           NO  YES NO
HR    MANAGER_INFO    DEPT_TYPE       NO  YES NO
HR    MANAGER_INFO    DEPTNO          NO  YES NO
HR    MANAGER_INFO    PRJ_LEVEL       NO  YES NO
HR    MANAGER_INFO    PROJNO          NO  YES NO


SQL> SELECT * FROM EMP_TAB;

EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM DEPTNO
----- ---------- --------- ---------- --------- ---------- ---------- ------
101 antony     manager          100 03-JAN-98      12000        100     10
111 john                                                                10
112 RAJ                                                                 11
12 anto                                                                12
13 palani                                                              13

SQL> insert into manager_info values('ramesh',14,14,14,14,14);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from manager_info;

ENAME      EMPNO  DEPT_TYPE DEPTNO  PRJ_LEVEL     PROJNO
---------- ----- ---------- ------ ---------- ----------
antony       101         10     10          1         10
john         111         10     10          1         10
RAJ          112         11     11          1         11
anto          12         12     12         12         12
palani        13         13     13         13         13
ramesh        14         14     14         14         14

6 rows selected.

SQL> select * from emp_tab;

EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM DEPTNO
----- ---------- --------- ---------- --------- ---------- ---------- ------
101 antony     manager          100 03-JAN-98      12000        100     10
111 john                                                                10
112 RAJ                                                                 11
12 anto                                                                12
13 palani                                                              13
14 ramesh                                                              14

6 rows selected.

SQL> select * from dept_tab;

DEPTNO DNAME           LOC               MGR_NO  DEPT_TYPE
------ --------------- ------------- ---------- ----------
 10 software        london               100         10
 11 hdware                                           11
 12                                                  12
 13                                                  13
 14                                                  14

SQL> select * from project_tab;

PRJ_LEVEL     PROJNO  RESP_DEPT
---------- ---------- ----------
      1         10
      1         11
     12         12
     13         13
     14         14

worked it out and send ur comments