クイックSQLによる変更履歴保存

quicksql-history.sql

-- create tables
create table emp (
    empno                          number,
    ename                          varchar2(50),
    job                            varchar2(50),
    mgr                            number,
    hiredate                       date,
    sal                            number,
    comm                           number,
    deptno                         number
)
;


-- history tracking
create sequence history_seq;
create table history (
    id                  number primary key,
    table_name          varchar2(128),
    column_name         varchar2(128),
    action              varchar2(1) check (action in ('I','U','D')),
    action_date         date,
    action_by           varchar2(255),
    data_type           varchar2(255),
    pk1                 number,
    tab_row_version     integer,
    old_vc              varchar2(4000),
    new_vc              varchar2(4000),
    old_number          number,
    new_number          number,
    old_date            date,
    new_date            date,
    old_ts              timestamp,
    new_ts              timestamp,
    old_tswtz           timestamp with time zone,
    new_tswtz           timestamp with time zone,
    old_tswltz          timestamp with local time zone,
    new_tswltz          timestamp with local time zone,
    old_clob            clob,
    new_clob            clob,
    old_blob            blob,
    new_blob            blob
)
/

create index history_idx1 on history (pk1);
create index history_idx2 on history (table_name, column_name);

create or replace view history_v as
select id,
       table_name,
       column_name,
       decode(action,'U','Update','D','Delete') action,
       action_date,
       action_by,
       pk1 table_primary_key,
       tab_row_version table_row_version,
       decode(data_type,
         'NUMBER',old_number||' > '||new_number,
         'VARCHAR2',substr(old_vc,1,50)||' > '||substr(new_vc,1,50),
         'DATE',to_char(old_date,'YYYYMMDD HH24:MI:SS')||' > '||to_char(new_date,'YYYYMMDD HH24:MI:SS'),
         'TIMESTAMP',to_char(old_ts,'YYYYMMDD HH24:MI:SS')||' > '||to_char(new_ts,'YYYYMMDD HH24:MI:SS'),
         'TIMESTAMP WITH TIMEZONE',to_char(old_tswtz,'YYYYMMDD HH24:MI:SS')||' > '||to_char(new_tswtz,'YYYYMMDD HH24:MI:SS'),
         'TIMESTAMP WITH LOCAL TIMEZONE',to_char(old_tswltz,'YYYYMMDD HH24:MI:SS')||' > '||to_char(new_tswltz,'YYYYMMDD HH24:MI:SS'),
         'BLOB','length '||sys.dbms_lob.getlength(old_blob)||' > '||' length '||sys.dbms_lob.getlength(new_blob),
         'CLOB',sys.dbms_lob.substr(old_vc,50,1)||' > '||sys.dbms_lob.substr(new_vc,50,1)
         ) change
from history
/

create or replace trigger emp_aud 
    after update or delete on emp
    for each row
declare
    t varchar2(128) := 'EMP';
    u varchar2(128) := nvl(sys_context('APEX$SESSION','APP_USER'),user);
begin
if updating then
    if (:old.empno is null and :new.empno is not null) or 
        (:old.empno is not null and :new.empno is null) or 
        :old.empno != :new.empno then 
        insert into history (
            id, table_name, column_name, pk1, tab_row_version, action, action_date, action_by, data_type, old_number, new_number
        ) values (
            history_seq.nextval, t, 'EMPNO', :old.id, null, 'U', sysdate, u, 'NUMBER', :old.empno, :new.empno);

    end if;
    if (:old.ename is null and :new.ename is not null) or 
        (:old.ename is not null and :new.ename is null) or 
        :old.ename != :new.ename then 
        insert into history (
            id, table_name, column_name, pk1, tab_row_version, action, action_date, action_by, data_type, old_vc, new_vc
        ) values (
            history_seq.nextval, t, 'ENAME', :old.id, null, 'U', sysdate, u, 'VARCHAR2', :old.ename, :new.ename);

    end if;
    if (:old.job is null and :new.job is not null) or 
        (:old.job is not null and :new.job is null) or 
        :old.job != :new.job then 
        insert into history (
            id, table_name, column_name, pk1, tab_row_version, action, action_date, action_by, data_type, old_vc, new_vc
        ) values (
            history_seq.nextval, t, 'JOB', :old.id, null, 'U', sysdate, u, 'VARCHAR2', :old.job, :new.job);

    end if;
    if (:old.mgr is null and :new.mgr is not null) or 
        (:old.mgr is not null and :new.mgr is null) or 
        :old.mgr != :new.mgr then 
        insert into history (
            id, table_name, column_name, pk1, tab_row_version, action, action_date, action_by, data_type, old_number, new_number
        ) values (
            history_seq.nextval, t, 'MGR', :old.id, null, 'U', sysdate, u, 'NUMBER', :old.mgr, :new.mgr);

    end if;
    if (:old.hiredate is null and :new.hiredate is not null) or 
        (:old.hiredate is not null and :new.hiredate is null) or 
        :old.hiredate != :new.hiredate then 
        insert into history (
            id, table_name, column_name, pk1, tab_row_version, action, action_date, action_by, data_type, old_date, new_date
        ) values (
            history_seq.nextval, t, 'HIREDATE', :old.id, null, 'U', sysdate, u, 'DATE', :old.hiredate, :new.hiredate);

    end if;
    if (:old.sal is null and :new.sal is not null) or 
        (:old.sal is not null and :new.sal is null) or 
        :old.sal != :new.sal then 
        insert into history (
            id, table_name, column_name, pk1, tab_row_version, action, action_date, action_by, data_type, old_number, new_number
        ) values (
            history_seq.nextval, t, 'SAL', :old.id, null, 'U', sysdate, u, 'NUMBER', :old.sal, :new.sal);

    end if;
    if (:old.comm is null and :new.comm is not null) or 
        (:old.comm is not null and :new.comm is null) or 
        :old.comm != :new.comm then 
        insert into history (
            id, table_name, column_name, pk1, tab_row_version, action, action_date, action_by, data_type, old_number, new_number
        ) values (
            history_seq.nextval, t, 'COMM', :old.id, null, 'U', sysdate, u, 'NUMBER', :old.comm, :new.comm);

    end if;
    if (:old.deptno is null and :new.deptno is not null) or 
        (:old.deptno is not null and :new.deptno is null) or 
        :old.deptno != :new.deptno then 
        insert into history (
            id, table_name, column_name, pk1, tab_row_version, action, action_date, action_by, data_type, old_number, new_number
        ) values (
            history_seq.nextval, t, 'DEPTNO', :old.id, null, 'U', sysdate, u, 'NUMBER', :old.deptno, :new.deptno);

    end if;
elsif deleting then
    insert into history (
        id, table_name, column_name, pk1, tab_row_version, action, action_date, action_by, data_type, old_number, new_number
    ) values (
        history_seq.nextval, t, 'EMPNO', :old.id, null, 'D', sysdate, u, 'NUMBER', :old.empno, :new.empno);
    insert into history (
        id, table_name, column_name, pk1, tab_row_version, action, action_date, action_by, data_type, old_vc, new_vc
    ) values (
        history_seq.nextval, t, 'ENAME', :old.id, null, 'D', sysdate, u, 'VARCHAR2', :old.ename, :new.ename);
    insert into history (
        id, table_name, column_name, pk1, tab_row_version, action, action_date, action_by, data_type, old_vc, new_vc
    ) values (
        history_seq.nextval, t, 'JOB', :old.id, null, 'D', sysdate, u, 'VARCHAR2', :old.job, :new.job);
    insert into history (
        id, table_name, column_name, pk1, tab_row_version, action, action_date, action_by, data_type, old_number, new_number
    ) values (
        history_seq.nextval, t, 'MGR', :old.id, null, 'D', sysdate, u, 'NUMBER', :old.mgr, :new.mgr);
    insert into history (
        id, table_name, column_name, pk1, tab_row_version, action, action_date, action_by, data_type, old_date, new_date
    ) values (
        history_seq.nextval, t, 'HIREDATE', :old.id, null, 'D', sysdate, u, 'DATE', :old.hiredate, :new.hiredate);
    insert into history (
        id, table_name, column_name, pk1, tab_row_version, action, action_date, action_by, data_type, old_number, new_number
    ) values (
        history_seq.nextval, t, 'SAL', :old.id, null, 'D', sysdate, u, 'NUMBER', :old.sal, :new.sal);
    insert into history (
        id, table_name, column_name, pk1, tab_row_version, action, action_date, action_by, data_type, old_number, new_number
    ) values (
        history_seq.nextval, t, 'COMM', :old.id, null, 'D', sysdate, u, 'NUMBER', :old.comm, :new.comm);
    insert into history (
        id, table_name, column_name, pk1, tab_row_version, action, action_date, action_by, data_type, old_number, new_number
    ) values (
        history_seq.nextval, t, 'DEPTNO', :old.id, null, 'D', sysdate, u, 'NUMBER', :old.deptno, :new.deptno);

end if;
end emp_aud;
/