This shows you the differences between two versions of the page.
Next revision | Previous revision | ||
kurs:employee_journal_trigger [2010/12/21 15:44] mh created |
kurs:employee_journal_trigger [2014/09/10 21:22] (current) |
||
---|---|---|---|
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> |