Thanks again David,
Since I am a newbie, looks like my questions evolve with answers. Last one I have is
1. how do we manage to add a new employee to either an existing or a totally new department at the same time. All we need to do is to supply the emp_name and dept_name. The values should get inserted by either creating a new deptName(and deptID)or by using an existing dept name. I assume we need the earlier newdept trigger, but the combination may conflict if deptname already exists.
Thanks
It won't if you understand how PL/SQL functions:
SQL> create table
2 newdept(deptID NUMBER NOT NULL, deptName varchar2(30));
Table created.
SQL>
SQL> alter table newdept
2 add constraint newdept_pk primary key(deptid);
Table altered.
SQL>
SQL> create table
2 newemp(empID number not null, empname varchar2(40), deptID NUMBER);
Table created.
SQL>
SQL> alter table newemp
2 add constraint newemp_pk primary key(empid);
Table altered.
SQL>
SQL> alter table newemp
2 add constraint newemp_dept_fk
3 foreign key(deptid) references newdept;
Table altered.
SQL>
SQL> create sequence newempseq
2 start with 1 increment by 1 nocycle nomaxvalue nocache order;
Sequence created.
SQL>
SQL> create sequence newdeptseq
2 start with 10 increment by 10 nocycle nomaxvalue nocache order;
Sequence created.
SQL>
SQL> create or replace trigger newempid_trg
2 before insert on newemp
3 for each row
4 begin
5 select newempseq.nextval
6 into :new.empid
7 from dual;
8 end;
9 /
Trigger created.
SQL>
SQL> create or replace trigger newdeptid_trg
2 before insert on newdept
3 for each row
4 begin
5 select newdeptseq.nextval
6 into :new.deptid
7 from dual;
8 end;
9 /
Trigger created.
SQL>
SQL> Create view E_VIEW as
2 Select empName, deptName
3 From newemp, newdept
4 Where newemp.deptID = newdept.deptID;
View created.
SQL>
SQL> CREATE or REPLACE procedure INSERT_TO_VIEW(
2 p_ename in varchar2,
3 p_dname in varchar)
4 IS
5 BEGIN
6 INSERT into E_VIEW values(p_ename, p_dname);
7 commit;
8 end;
9 /
Procedure created.
SQL>
SQL> create or replace trigger e_view_insert
2 instead of insert on e_view
3 for each row
4 declare
5 dept_id number;
6 begin
7 select deptid
8 into dept_id
9 from newdept
10 where deptname = :new.deptname;
11
12 insert into newemp
13 (empname, deptid)
14 values(:new.empname, dept_id);
15
16 exception
17 when no_data_found then
18 insert into newdept
19 (deptname)
20 values(:new.deptname)
21 returning deptid into dept_id;
22
23 insert into newemp
24 (empname, deptid)
25 values(:new.empname, dept_id);
26
27 end;
28 /
Trigger created.
SQL>
SQL> CREATE or REPLACE procedure INPUT_RECORDS(
2 p_ename in varchar2,
3 p_dname in varchar2)
4 IS
5 BEGIN
6 INSERT into newemp(empid, empname, deptID)
7 select newEmpSeq.nextval, p_ename, deptID
8 FROM
9 newdept where deptname = p_dname;
10 commit;
11 end;
12 /
Procedure created.
SQL>
SQL> insert into newdept
2 (deptname)
3 values('Marketing');
1 row created.
SQL>
SQL> Begin
2 INSERT_TO_VIEW('Mark', 'Sales');
3 INSERT_TO_VIEW('Zappo', 'Finance');
4 end;
5 /
PL/SQL procedure successfully completed.
SQL>
SQL> Begin
2 INPUT_RECORDS('Clyde', 'Marketing');
3 end;
4 /
PL/SQL procedure successfully completed.
SQL>
SQL> select *
2 from e_view;
EMPNAME DEPTNAME
---------------------------------------- ------------------------------
Mark Sales
Zappo Finance
Clyde Marketing
SQL>
SQL> select *
2 from newemp;
EMPID EMPNAME DEPTID
---------- ---------------------------------------- ----------
1 Mark 20
2 Zappo 30
4 Clyde 10
SQL>
SQL> select *
2 from newdept;
DEPTID DEPTNAME
---------- ------------------------------
10 Marketing
20 Sales
30 Finance
SQL>
Notice how the processing shifted in the INSTEAD OF trigger when the department wasn't found in the dept table.
David Fitzjarrell