kurs:instead_of_trigger
Differences
This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
kurs:instead_of_trigger [2010/05/18 11:15] 127.0.0.1 external edit |
kurs:instead_of_trigger [2014/09/10 21:22] (current) |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| <code> | <code> | ||
| - | create table new_emps as select | + | CREATE OR REPLACE VIEW emp_dep ( |
| - | employee_id, first_name, last_name, salary, department_id | + | |
| - | from employees | + | |
| - | + | ||
| - | + | ||
| - | create table new_deps as select | + | |
| - | department_id, department_name from departments | + | |
| - | </code> | + | |
| - | + | ||
| - | <code> | + | |
| - | CREATE OR REPLACE VIEW view_emp_dep ( | + | |
| first_name, | first_name, | ||
| last_name, | last_name, | ||
| - | department_name, | + | department_name ) |
| - | salary ) | + | |
| AS | AS | ||
| - | SELECT new_emps.first_name, | + | select |
| - | new_emps.last_name, | + | first_name, |
| - | new_deps.department_name, | + | last_name, |
| - | new_emps.salary | + | department_name |
| - | FROM new_deps, new_emps | + | from departments d join employees e |
| - | WHERE ((new_emps.department_id = new_deps.department_id(+))) | + | on d.department_id = e.department_id |
| / | / | ||
| - | </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 | + | -- Triggers for EMP_DEP |
| + | |||
| + | CREATE OR REPLACE TRIGGER emp_dep_instead_trg | ||
| INSTEAD OF | INSTEAD OF | ||
| INSERT OR DELETE OR UPDATE | INSERT OR DELETE OR UPDATE | ||
| - | ON view_emp_dep | + | ON emp_dep |
| REFERENCING NEW AS NEW OLD AS OLD | 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; | ||
| + | / | ||
| + | </code> | ||
| + | |||
| + | <code> | ||
| + | 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 | ||
| + | </code> | ||
| + | |||
| + | <code> | ||
| 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 89: | ||
| DEPARTMENT_ID) | DEPARTMENT_ID) | ||
| values ( | values ( | ||
| - | employees_seq.nextval, | ||
| :new.first_name, | :new.first_name, | ||
| :new.last_name, | :new.last_name, | ||
| Line 54: | Line 94: | ||
| 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 111: | ||
| end if; | end if; | ||
| end; | end; | ||
| - | / | ||
| </code> | </code> | ||
kurs/instead_of_trigger.1274174146.txt.gz · Last modified: 2014/09/10 21:22 (external edit)
Page Tools
Except where otherwise noted, content on this wiki is licensed under the following license: CC Attribution-Share Alike 4.0 International
