Differences

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

--- kurs:instead_of_trigger [2010/06/09 15:39]
mh
+++ kurs:instead_of_trigger [2014/09/10 21:22]
@@ Line 1: / Line 1: @@
-<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