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;