dokuwiki

CREATE OR REPLACE VIEW emp_dep (
   first_name,
   last_name,
   department_name )
AS
select 
    first_name, 
    last_name, 
    department_name 
  from departments d join employees e
  on d.department_id = e.department_id
/


-- Triggers for EMP_DEP

CREATE OR REPLACE TRIGGER emp_dep_instead_trg
 INSTEAD OF
  INSERT OR DELETE OR UPDATE
 ON emp_dep
REFERENCING NEW AS NEW OLD AS OLD
begin
/*
first_name
last_name
department_name
*/
if inserting then
  insert into employees
    (employee_id,
     first_name,
     last_name,
     email,
     hire_date,
     job_id,
     department_id
     ) values (
     employees_seq.nextval,
     :new.first_name,
     :new.last_name,
     upper(:new.last_name),
     sysdate,
     'IT_PROG',
     (select department_id from departments
        where department_name = :new.department_name)
     );
end if;
end;
/

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;