You Asked

I have a problem with that i have table emp_table(name, salary, department_name)

I want to move data from this table to two tables employees(name,salary,department_id), department(department_name)

And i putted a trigger on departments to make department_id from a sequence

The problem is how to till the cursor that if the department_name is already exist don’t give it number again or don’t insert it again in the table

And how to pass department_id to employee if desired

and Connor said...

You could do something like this

SQL> create table emp_dept as select ename, dname, sal
  2  from scott.emp e, scott.dept d
  3  where e.deptno = d.deptno;

Table created.

SQL>
SQL> alter table emp_dept add constraint emp_dept_pk primary key ( ename );

Table altered.

SQL>
SQL> create sequence dept_seq;

Sequence created.

SQL> create sequence emp_seq;

Sequence created.

SQL>
SQL> create table dept
  2  as select dept_seq.nextval deptno, dname from scott.dept;

Table created.

SQL>
SQL> create table emp
  2  as select emp_seq.nextval empno, ename , sal from scott.emp;

Table created.

SQL>
SQL>
SQL> alter table dept add constraint dept_pk primary key ( deptno);

Table altered.

SQL> alter table dept add constraint dept_uq unique ( dname);

Table altered.

SQL>
SQL> alter table emp add constraint emp_pk primary key ( empno);

Table altered.

SQL> alter table emp add constraint emp_uq unique ( ename);

Table altered.

SQL>
SQL> alter table emp_dept modify dname not null;

Table altered.

SQL> alter table emp_dept modify ename not null;

Table altered.

SQL>
SQL> alter table emp_dept add constraint emp_dept_fk1 foreign key (dname ) references dept ( dname );

Table altered.

SQL> alter table emp_dept add constraint emp_dept_fk2 foreign key (ename ) references emp ( ename );

Table altered.

SQL>
SQL>
SQL> create or replace
  2  trigger emp_dept_trg
  3  before insert or update or delete on emp_dept
  4  for each row
  5  declare
  6    l_empno int;
  7  begin
  8    if inserting then
  9        insert into dept
 10        select dept_seq.nextval, :new.dname
 11        from   dual
 12        where  not exists ( select null from dept where dname = :new.dname );
 13
 14        insert into emp
 15        values (emp_seq.nextval, :new.ename, :new.sal);
 16    end if;
 17
 18    if updating then
 19        if :new.ename = :old.ename
 20        then
 21          update emp
 22          set    sal = :new.sal
 23          where  ename = :new.ename;
 24        else
 25          delete from emp where ename = :old.ename
 26          returning empno into l_empno;
 27          insert into emp
 28          values (l_empno, :new.ename, :new.sal);
 29        end if;
 30    end if;
 31
 32    if deleting then
 33        delete from emp
 34        where  ename = :old.ename;
 35    end if;
 36
 37  end;
 38  /

Trigger created.

SQL>
SQL> select * from emp_dept;

ENAME      DNAME                 SAL
---------- -------------- ----------
CLARK      ACCOUNTING           2450
KING       ACCOUNTING           5000
MILLER     ACCOUNTING           1300
JONES      RESEARCH             2975
FORD       RESEARCH             3000
ADAMS      RESEARCH             1100
SMITH      RESEARCH              800
SCOTT      RESEARCH             3000
WARD       SALES                1250
TURNER     SALES                1500
ALLEN      SALES                1600
JAMES      SALES                 950
BLAKE      SALES                2850
MARTIN     SALES                1250

14 rows selected.

SQL>
SQL> insert into emp_dept values ( 'BROWN','NEW_DEPT',1000);

1 row created.

SQL> select * from emp;

     EMPNO ENAME             SAL
---------- ---------- ----------
         1 SMITH             800
         2 ALLEN            1600
         3 WARD             1250
         4 JONES            2975
         5 MARTIN           1250
         6 BLAKE            2850
         7 CLARK            2450
         8 SCOTT            3000
         9 KING             5000
        10 TURNER           1500
        11 ADAMS            1100
        12 JAMES             950
        13 FORD             3000
        14 MILLER           1300
        15 BROWN            1000

15 rows selected.

SQL> select * from dept;

    DEPTNO DNAME
---------- --------------
         1 ACCOUNTING
         2 RESEARCH
         3 SALES
         4 OPERATIONS
         5 NEW_DEPT

5 rows selected.

SQL>
SQL> delete from emp_Dept where ename = 'CLARK';

1 row deleted.

SQL> select * from emp;

     EMPNO ENAME             SAL
---------- ---------- ----------
         1 SMITH             800
         2 ALLEN            1600
         3 WARD             1250
         4 JONES            2975
         5 MARTIN           1250
         6 BLAKE            2850
         8 SCOTT            3000
         9 KING             5000
        10 TURNER           1500
        11 ADAMS            1100
        12 JAMES             950
        13 FORD             3000
        14 MILLER           1300
        15 BROWN            1000

14 rows selected.

SQL>
SQL> update emp_dept
  2  set sal = sal*100
  3  where ename = 'SMITH';

1 row updated.

SQL> select * from emp;

     EMPNO ENAME             SAL
---------- ---------- ----------
         1 SMITH           80000
         2 ALLEN            1600
         3 WARD             1250
         4 JONES            2975
         5 MARTIN           1250
         6 BLAKE            2850
         8 SCOTT            3000
         9 KING             5000
        10 TURNER           1500
        11 ADAMS            1100
        12 JAMES             950
        13 FORD             3000
        14 MILLER           1300
        15 BROWN            1000

14 rows selected.

SQL>
SQL> update emp_dept
  2  set ename = 'JONES'
  3  where ename = 'SMITH';
update emp_dept
       *
ERROR at line 1:
ORA-00001: unique constraint (MCDONAC.EMP_UQ) violated
ORA-06512: at "MCDONAC.EMP_DEPT_TRG", line 23
ORA-04088: error during execution of trigger 'MCDONAC.EMP_DEPT_TRG'


SQL>
SQL> update emp_dept
  2  set ename = 'PETERS'
  3  where ename = 'SMITH';

1 row updated.

SQL> select * from emp;

     EMPNO ENAME             SAL
---------- ---------- ----------
         2 ALLEN            1600
         3 WARD             1250
         4 JONES            2975
         5 MARTIN           1250
         6 BLAKE            2850
         8 SCOTT            3000
         9 KING             5000
        10 TURNER           1500
        11 ADAMS            1100
        12 JAMES             950
        13 FORD             3000
        14 MILLER           1300
        15 BROWN            1000
         1 PETERS          80000

14 rows selected.

SQL>

It is critical to the have constraints in place as shown above to handle concurrency of sessions. Even so, you are still introducing some contention points (eg if two people insert an employee with the same *new* department, one of them will block until the other commits).

I like that you're moving to the better design, but I view this as a temporary solution - better to move over and retire the original table if possible.

Rating

(1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Ayah Mamdouh, December 18, 2018 - 5:16 am UTC

Thank you but i want to make a procedure that make this case and to put it into package and call it from a trigger, so i want to make an anonymous block to do what i need the convert it to a procedure.

In your answer i can’t find that how to pass the department_id to the employee

and that with every insert in emp_temp the department name will go to the department table althougth it exist there but this time with a new sequence number ?? In the condition you write where not exists(select null from dept where dname=:new.dname) ?

December 19, 2018 - 1:47 am UTC

Thank you but i want to make a procedure that make this case and to put it into package and call it from a trigger, so i want to make an anonymous block to do what i need the convert it to a procedure.

Sorry I dont understand what you're asking here

In your answer i can’t find that how to pass the department_id to the employee

My mistake. Line 14 could be recoded as:

insert into emp
values (emp_seq.nextval, 
        :new.ename, 
        :new.sal,
        ( select deptno from dept where dname = :new.dname)
        );

where emp has had deptno added as a column.

and a similar alteration for the updating clause

and that with every insert in emp_temp the department name will go to the department table althougth it exist there but this time with a new sequence number ?? In the condition you write where not exists(select null from dept where dname=:new.dname) ?

We will only insert a new dept if one with that name does not yet already exist.

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

More to Explore

PL/SQL demos

PL/SQL docs

Business case problem

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

More to Explore

PL/SQL demos

PL/SQL docs