kurs:employee_journal_trigger
Differences
This shows you the differences between two versions of the page.
|
kurs:employee_journal_trigger [2010/12/22 14:51] mh |
kurs:employee_journal_trigger [2014/09/10 21:22] |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| - | jn table | ||
| - | <code> | ||
| - | CREATE TABLE employees_jn | ||
| - | ( | ||
| - | id NUMBER(6,0) , | ||
| - | jn_type VARCHAR2(1 BYTE), | ||
| - | employee_id NUMBER(6,0) , | ||
| - | first_name VARCHAR2(20 BYTE), | ||
| - | last_name VARCHAR2(25 BYTE) , | ||
| - | email VARCHAR2(25 BYTE) , | ||
| - | phone_number VARCHAR2(20 BYTE), | ||
| - | hire_date DATE , | ||
| - | job_id VARCHAR2(10 BYTE) , | ||
| - | job_title VARCHAR2(100 BYTE) , | ||
| - | old_salary NUMBER(8,2), | ||
| - | new_salary NUMBER(8,2), | ||
| - | commission_pct NUMBER(2,2), | ||
| - | manager_id NUMBER(6,0), | ||
| - | manager_name VARCHAR2(100 BYTE), | ||
| - | department_id NUMBER(4,0), | ||
| - | department_name VARCHAR2(100 BYTE), | ||
| - | valid_from date, | ||
| - | valid_to date | ||
| - | ) | ||
| - | </code> | ||
| - | |||
| - | Trigger | ||
| - | |||
| - | <code> | ||
| - | DECLARE | ||
| - | v_jn_type VARCHAR2 (1); | ||
| - | BEGIN | ||
| - | v_jn_type := | ||
| - | CASE | ||
| - | WHEN INSERTING THEN 'I' | ||
| - | WHEN UPDATING THEN 'U' | ||
| - | ELSE 'D' | ||
| - | END; | ||
| - | |||
| - | INSERT INTO employees_jn (id, | ||
| - | jn_type, | ||
| - | employee_id, | ||
| - | first_name, | ||
| - | last_name, | ||
| - | email, | ||
| - | phone_number, | ||
| - | hire_date, | ||
| - | job_id, | ||
| - | job_title, | ||
| - | old_salary, | ||
| - | new_salary, | ||
| - | commission_pct, | ||
| - | manager_id, | ||
| - | manager_name, | ||
| - | department_id, | ||
| - | department_name, | ||
| - | valid_from, | ||
| - | valid_to) | ||
| - | VALUES (hr_seq.NEXTVAL, | ||
| - | v_jn_type, | ||
| - | NVL (:new.employee_id, :old.employee_id), | ||
| - | NVL (:new.first_name, :old.first_name), | ||
| - | NVL (:new.last_name, :old.last_name), | ||
| - | NVL (:new.email, :old.email), | ||
| - | NVL (:new.phone_number, :old.phone_number), | ||
| - | NVL (:new.hire_date, :old.hire_date), | ||
| - | NVL (:new.job_id, :old.job_id), | ||
| - | (SELECT job_title | ||
| - | FROM jobs | ||
| - | WHERE job_id = NVL (:new.job_id, :old.job_id)), | ||
| - | :old.salary, | ||
| - | :new.salary, | ||
| - | NVL (:new.commission_pct, :old.commission_pct), | ||
| - | NVL (:new.manager_id, :old.manager_id), | ||
| - | (SELECT first_name || ' ' || last_name | ||
| - | FROM employees | ||
| - | WHERE employee_id = | ||
| - | NVL (:new.manager_id, :old.manager_id)), | ||
| - | NVL (:new.department_id, :old.department_id), | ||
| - | (SELECT department_name | ||
| - | FROM departments | ||
| - | WHERE department_id = | ||
| - | NVL (:new.department_id, :old.department_id)), | ||
| - | NVL (:new.hire_date, :old.hire_date), | ||
| - | SYSDATE); | ||
| - | END; | ||
| - | </code> | ||
kurs/employee_journal_trigger.txt ยท Last modified: 2014/09/10 21:22 (external edit)
Page Tools
Except where otherwise noted, content on this wiki is licensed under the following license: CC Attribution-Share Alike 4.0 International
