User Tools

Site Tools


kurs:instead_of_trigger

Differences

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

Link to this comparison view

Both sides previous revision Previous revision
kurs:instead_of_trigger [2010/06/09 15:39]
mh
kurs:instead_of_trigger [2011/12/01 15:03]
mh
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 ​
kurs/instead_of_trigger.txt ยท Last modified: 2014/09/10 21:22 (external edit)