/* The package fills the table of employees' state changing history.
 * Observed tables: emp_states, emp_position, emp_departments.
 */

-- Audit trigger.

create or replace trigger observe_employee_states
    after insert or update or delete 
    on emp_states
    for each row
begin
   employee_observer.observe( :NEW.emp_id, :NEW.state_id, NULL, NULL );  
end;
/
/*
create or replace trigger observe_employee_positions
    after insert or update or delete 
    on emp_positions
    for each row
begin
   employee_observer.observe( :NEW.emp_id, NULL, NULL, :NEW.pos_id );
end;
/
create or replace trigger observe_employee_departments
    after insert or update or delete 
    on emp_departments
    for each row
begin
   employee_observer.observe( :NEW.emp_id, NULL, :NEW.dep_id, NULL );  
end;
/
*/
-- Audit table.
create table audit_employees(
    id          number not null,
    emp_id      number not null,
    message     varchar2(100),
    change_date date
);
-- Sequence for audit table's ID.
create sequence ae_id_seq
   start with   1
   increment by 1
   cache        10;

create or replace package employee_observer as
    procedure observe(  emp_id          number,
                        cur_state_id    number,
                        cur_dep_id      number,
                        cur_pos_id      number
    );
end employee_observer;
/

create or replace package body employee_observer as
    procedure observe(
            emp_id          number,
            cur_state_id    number,
            cur_dep_id      number,
            cur_pos_id      number
    ) as
        message  varchar2(100)   := NULL;
    begin
        if cur_state_id is not NULL then
         select states.name
         into message 
            from states
            where states.id = cur_state_id;
        elsif cur_dep_id is not NULL then
            select departments.name
            into message
                from departments
                where departments.id = emp_dep_id;
        elsif cur_pos_id is not NULL then
            select positions.name
            into message
                from positions
                where positions.id = emp_pos_id;
        end if;

        insert into audit_employees( id, emp_id, message, change_date )
        values(
            ae_id_seq.nextval, emp_id, message, sysdate
        );
    end;
end employee_observer;
/