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;