dokuwiki

jn table

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 
    )

Trigger

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;