Differences

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

--- kurs:instead_of_trigger [2010/06/09 15:37]
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>
 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>

dokuwiki

User Tools

Site Tools

Differences

Page Tools