In sweet memories of my ever loving brother "kutty thambi " ARUN KUMAR

Tuesday, August 4, 2009

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

No comments:

 
Share/Bookmark