Scribd
Upload
186 views3 pages

Create History Table For Existing Table

This document defines tables, triggers, and views to implement auditing for the blue table. It creates the blue table, triggers for insert/update to set timestamps and IDs, a history table to track changes, and views to display the audit data. Indexes are added to the history table for performance.

Uploaded by

Rennie Ramlochan
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
186 views3 pages

Create History Table For Existing Table

This document defines tables, triggers, and views to implement auditing for the blue table. It creates the blue table, triggers for insert/update to set timestamps and IDs, a history table to track changes, and views to display the audit data. Indexes are added to the history table for performance.

Uploaded by

Rennie Ramlochan
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 3

-- create tables

create table blue (


id number not null constraint blue_id_pk primary
key,
bname varchar2(255),
btag varchar2(4000),
created date not null,
created_by varchar2(255) not null,
updated date not null,
updated_by varchar2(255) not null
)
;

-- triggers
create or replace trigger blue_biu
before insert or update
on blue
for each row
begin
if :new.id is null then
:new.id := to_number(sys_guid(), 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');
end if;
if inserting then
:new.created := sysdate;
:new.created_by := nvl(sys_context('APEX$SESSION','APP_USER'),user);
end if;
:new.updated := sysdate;
:new.updated_by := nvl(sys_context('APEX$SESSION','APP_USER'),user);
end blue_biu;
/

-- history tracking
create sequence history_seq_blue;
create table history_blue (
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_idx11 on history_blue (pk1);


create index history_idx22 on history_blue (table_name, column_name);

create or replace view history_v_blue 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,'DD-MON-YYY HH24:MI:SS')||' > '||
to_char(new_date,'DD-MON-YYY HH24:MI:SS'),
'TIMESTAMP',to_char(old_ts,'DD-MON-YYY HH24:MI:SS')||' > '||
to_char(new_ts,'DD-MON-YYY HH24:MI:SS'),
'TIMESTAMP WITH TIMEZONE',to_char(old_tswtz,'DD-MON-YYY HH24:MI:SS')||' >
'||to_char(new_tswtz,'DD-MON-YYY HH24:MI:SS'),
'TIMESTAMP WITH LOCAL TIMEZONE',to_char(old_tswltz,'DD-MON-YYY
HH24:MI:SS')||' > '||to_char(new_tswltz,'DD-MON-YYY HH24:MI:SS'),
'BLOB','length '||dbms_lob.getlength(old_blob)||' > '||' length '||
dbms_lob.getlength(new_blob),
'CLOB',dbms_lob.substr(old_vc,50,1)||' > '||dbms_lob.substr(new_vc,50,1)
) change
from history
/

create or replace trigger blue_aud


after update or delete on blue
for each row
declare
t varchar2(128) := 'BLUE';
u varchar2(128) := nvl(sys_context('APEX$SESSION','APP_USER'),user);
begin
if updating then
if (:old.id is null and :new.id is not null) or
(:old.id is not null and :new.id is null) or
:old.id != :new.id 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, 'ID', :old.id, null, 'U', sysdate, u, 'NUMBER',
:old.id, :new.id);

end if;
if (:old.bname is null and :new.bname is not null) or
(:old.bname is not null and :new.bname is null) or
:old.bname != :new.bname 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, 'BNAME', :old.id, null, 'U', sysdate, u,
'VARCHAR2', :old.bname, :new.bname);

end if;
if (:old.btag is null and :new.btag is not null) or
(:old.btag is not null and :new.btag is null) or
:old.btag != :new.btag 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, 'BTAG', :old.id, null, 'U', sysdate, u,
'VARCHAR2', :old.btag, :new.btag);

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, 'ID', :old.id, null, 'D', sysdate, u, 'NUMBER',
:old.id, :new.id);
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, 'BNAME', :old.id, null, 'D', sysdate, u,
'VARCHAR2', :old.bname, :new.bname);
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, 'BTAG', :old.id, null, 'D', sysdate, u, 'VARCHAR2',
:old.btag, :new.btag);

end if;
end blue_aud;
/

-- load data

You might also like