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/06/09 15:38] mh |
kurs:instead_of_trigger [2014/09/10 21:22] (current) |
||
---|---|---|---|
Line 1: | Line 1: | ||
+ | <code> | ||
+ | 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; | ||
+ | / | ||
+ | </code> | ||
+ | |||
<code> | <code> | ||
create table my_emp as select | create table my_emp as select | ||
Line 7: | Line 59: | ||
department_id, department_name from departments | department_id, department_name from departments | ||
- | CREATE OR REPLACE FORCE VIEW "HR"."EMPDEP" ("FIRST_NAME", "LAST_NAME", "SALARY", "DEPARTMENT_NAME") AS | + | CREATE OR REPLACE VIEW EMPDEP ( |
- | select first_name, last_name, salary, department_name from my_emp e, my_dep d | + | FIRST_NAME, LAST_NAME, SALARY, DEPARTMENT_NAME) AS |
- | where e.department_id = d.department_id | + | select first_name, last_name, salary, |
+ | department_name from my_emp e, my_dep d | ||
+ | where e.department_id = d.department_id | ||
</code> | </code> | ||