dokuwiki

This is an old revision of the document!

---

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


create table new_deps as select 
department_id, department_name from departments

CREATE OR REPLACE VIEW view_emp_dep (
   first_name,
   last_name,
   department_name,
   salary )
AS
SELECT new_emps.first_name,
       new_emps.last_name,
       new_deps.department_name,
       new_emps.salary
  FROM new_deps, new_emps
 WHERE ((new_emps.department_id = new_deps.department_id(+)))
/

-- Start of DDL Script for Trigger HR.TRG_VIEW_EMP_DEP
-- Generated 13.11.2008 12:57:27 from HR@ORCL

CREATE OR REPLACE TRIGGER trg_view_emp_dep
 INSTEAD OF
  INSERT OR DELETE OR UPDATE
 ON view_emp_dep
REFERENCING NEW AS NEW OLD AS OLD
declare
 dep_id departments.department_id%type;
begin
  if inserting then
  select department_id into dep_id from new_deps where
    department_name = :new.department_name;
    
  insert into new_emps (
    EMPLOYEE_ID,
    FIRST_NAME,
    LAST_NAME,
    SALARY,
    DEPARTMENT_ID)
  values (
    employees_seq.nextval,
    :new.first_name,
    :new.last_name,
    :new.salary,
    dep_id);
  elsif deleting then
    delete from new_emps where
      :old.last_name  = last_name and
      :old.first_name = first_name;
  else
    update new_emps 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 new_deps where
        department_name = :old.department_name)
    where
     first_name = :old.first_name and
     last_name  = :old.last_name;

  end if;
end;
/