This shows you the differences between two versions of the page.
Both sides previous revision Previous revision Next revision | Previous revision Next revision Both sides next revision | ||
kurs:instead_of_trigger [2010/05/18 11:15] 127.0.0.1 external edit |
kurs:instead_of_trigger [2010/06/09 15:39] mh |
||
---|---|---|---|
Line 1: | Line 1: | ||
<code> | <code> | ||
- | create table new_emps as select | + | create table my_emp as select |
employee_id, first_name, last_name, salary, department_id | employee_id, first_name, last_name, salary, department_id | ||
from employees | from employees | ||
- | + | create table my_dep as select | |
- | create table new_deps as select | + | |
department_id, department_name from departments | department_id, department_name from departments | ||
- | </code> | ||
- | <code> | + | CREATE OR REPLACE VIEW EMPDEP ( |
- | CREATE OR REPLACE VIEW view_emp_dep ( | + | FIRST_NAME, LAST_NAME, SALARY, DEPARTMENT_NAME) AS |
- | first_name, | + | select first_name, last_name, salary, |
- | last_name, | + | department_name from my_emp e, my_dep d |
- | department_name, | + | where e.department_id = d.department_id |
- | 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(+))) | + | |
- | / | + | |
</code> | </code> | ||
<code> | <code> | ||
- | -- 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 | declare | ||
- | dep_id departments.department_id%type; | + | dep_id my_dep.department_id%type; |
begin | 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 | if inserting then | ||
- | select department_id into dep_id from new_deps where | + | insert into my_emp ( |
- | department_name = :new.department_name; | + | |
- | + | ||
- | insert into new_emps ( | + | |
- | EMPLOYEE_ID, | + | |
FIRST_NAME, | FIRST_NAME, | ||
LAST_NAME, | LAST_NAME, | ||
Line 48: | Line 37: | ||
DEPARTMENT_ID) | DEPARTMENT_ID) | ||
values ( | values ( | ||
- | employees_seq.nextval, | ||
:new.first_name, | :new.first_name, | ||
:new.last_name, | :new.last_name, | ||
Line 54: | Line 42: | ||
dep_id); | dep_id); | ||
elsif deleting then | elsif deleting then | ||
- | delete from new_emps where | + | delete from my_emp where |
:old.last_name = last_name and | :old.last_name = last_name and | ||
:old.first_name = first_name; | :old.first_name = first_name; | ||
else | else | ||
- | update new_emps set | + | update my_emp set |
first_name = nvl(:new.first_name, :old.first_name), | first_name = nvl(:new.first_name, :old.first_name), | ||
last_name = nvl(:new.last_name, :old.last_name), | last_name = nvl(:new.last_name, :old.last_name), | ||
salary = :new.salary, | salary = :new.salary, | ||
department_id = | department_id = | ||
- | (select department_id from new_deps where | + | (select department_id from my_dep where |
- | department_name = :old.department_name) | + | department_name = nvl(:new.department_name,:old.department_name)) |
where | where | ||
first_name = :old.first_name and | first_name = :old.first_name and | ||
Line 71: | Line 59: | ||
end if; | end if; | ||
end; | end; | ||
- | / | ||
</code> | </code> | ||