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; /