kurs:employee_journal_trigger
Differences
This shows you the differences between two versions of the page.
|
kurs:employee_journal_trigger [2010/12/21 15:44] mh created |
kurs:employee_journal_trigger [2010/12/22 14:51] mh |
||
|---|---|---|---|
| Line 24: | Line 24: | ||
| valid_to 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> | </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
