dokuwiki

This is an old revision of the document!

---

create table my_emp as select 
employee_id, first_name, last_name, salary, department_id
from employees

create table my_dep as select 
department_id, department_name from departments

CREATE OR REPLACE VIEW EMPDEP (
   FIRST_NAME, LAST_NAME, SALARY, DEPARTMENT_NAME) AS 
  select first_name, last_name, salary, 
  department_name from my_emp e, my_dep d
  where e.department_id = d.department_id

declare
 dep_id my_dep.department_id%type;
begin
  if :new.department_name is not null then
    begin
     select department_id into dep_id from my_dep where
     department_name = :new.department_name;
    exception
     when no_data_found then
       insert into my_dep (department_id, department_name)
       values (hr_seq.nextval,:new.department_name)
       returning department_id into dep_id;
    end;
  end if;

  if inserting then
  insert into my_emp (  
    FIRST_NAME,
    LAST_NAME,
    SALARY,
    DEPARTMENT_ID)
  values (
    :new.first_name,
    :new.last_name,
    :new.salary,
    dep_id);
  elsif deleting then
    delete from my_emp where
      :old.last_name  = last_name and
      :old.first_name = first_name;
  else
    update my_emp set
      first_name = nvl(:new.first_name, :old.first_name),
      last_name  = nvl(:new.last_name,  :old.last_name),
      salary     = :new.salary,
      department_id =
       (select department_id from my_dep where
        department_name = nvl(:new.department_name,:old.department_name))
    where
     first_name = :old.first_name and
     last_name  = :old.last_name;

  end if;
end;