Differences

This shows you the differences between two versions of the page.

--- kurs:instead_of_trigger [2011/12/01 15:03]
mh
+++ kurs:instead_of_trigger [2014/09/10 21:22]
@@ 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>
-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
- 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;
-</code>

dokuwiki

User Tools

Site Tools

Differences

Page Tools