Wednesday, January 28, 2009

Table History - with a Twist

The Task

The other day colleagues wanted to track changes of rows in a table. They wanted to fill a history table which contains all the columns from the original table plus a timestamp. You would immediately think "triggers". Well yes, but with INSERT and UPDATE triggers you would get an entry per change while colleagues wanted an entry per transaction only. This makes matters slightly more complicated.

First Solution?

I thought, this is easy: just record changes in a package variable and then write them to the history table on COMMIT. Of course, you need INSERT and UPDATE triggers - and a COMMIT trigger. It turns out, while you will find plenty on the matter the beast simply does not exist.

Final Solution!

Well the solution is not that far away. Ingredients are
  1. a history table sharing the original table's columns plus a TIMESTAMP column but without primary key,
  2. INSERT and UPDATE triggers,
  3. a global temporary table,
  4. ROWID.
Now stir thoroughly and everything falls into place. The final solution works like this: when the fist change of a record in the original table appears in a transaction a history record is created in the history table and the ROWID of it is stored in the global temporary table. For all subsequent changes of the same record (identified by its primary key) the history record is looked up via the global temporary table and then updated. That way no more than one history record is created per transaction per original record. When the transaction terminates (either via COMMIT or ROLLBACK) the global temporary table is cleared.

Here is a script that you can execute in SQL Plus and which will demonstrate how this works. You can concatenate all three parts and put them in a single file for execution with SQL Plus.

Setup:


alter session set nls_timestamp_format = 'YYYY-MM-DD HH24:MI.SS.FF3';
prompt "---------- START ---------- "
column ts format a30
set feedback on
set serveroutput on

create sequence s1
start with 1 increment by 1 cache 20
/
create table t1 (
id number(10) primary key,
name1 varchar2(15 char),
name2 varchar2(15 char)
)
/
create table t1_hist (
ts timestamp(3) not null,
id number(10),
name1 varchar2(15 char),
name2 varchar2(15 char),
constraint pk2 primary key ( id, ts ) using index compress 1
)
/
create global temporary table t1_hist_ptr (
id number(10) primary key,
rid rowid not null -- we use rowid for efficient row access
)
on commit delete rows
/
create trigger t1_insert
after insert or update of id, name1, name2
on t1
for each row
declare
rid rowid;
begin
if updating and :new.id <> :old.id then
-- PK has changed!
update t1_hist_ptr set
id = :new.id
where id = :old.id;

dbms_output.put_line('===> updated id ' || :old.id || ' -> ' || :new.id );
end if;

update t1_hist set
ts = systimestamp,
id = :new.id, -- important!
name1 = :new.name1,
name2 = :new.name2
where t1_hist.rowid = ( select rid from t1_hist_ptr where id = :new.id );

dbms_output.put_line('===> updated rowcount ' || sql%rowcount);

if sql%rowcount = 0 then
-- not there yet, need to insert history record
insert into t1_hist values ( systimestamp, :new.id, :new.name1, :new.name2 )
returning rowid into rid;
insert into t1_hist_ptr values ( :new.id, rid );
end if;
end t1_insert;
/
show errors trigger t1_insert


Update tests:


/*
* MAIN START
*/
-- initially empty
select t1_hist.*
from t1_hist
order by t1_hist.ts, t1_hist.id
/
-- inserts
set echo on
insert into t1 values ( s1.nextval, '1 original', 'bar' )
/
insert into t1 values ( s1.nextval, '2 original', 'buz' )
/
set echo off
select t1_hist.*
from t1_hist
order by t1_hist.ts, t1_hist.id
/
-- update
set echo on
update t1 set name1 = '1 updated 1' where id = 1
/
set echo on
update t1 set name1 = '1 updated 2' where id = 1
/
set echo off
select t1_hist.*
from t1_hist
order by t1_hist.ts, t1_hist.id
/
commit
/
-- update with rollback
set echo on
update t1 set name1 = '2 updated 3' where id = 2
/
set echo off
select t1_hist.*
from t1_hist
order by t1_hist.ts, t1_hist.id
/
rollback
/
select t1_hist.*
from t1_hist
order by t1_hist.ts, t1_hist.id
/
-- insert with dummy id
insert into t1 values ( -1, '-1 original', 'dummy entry' )
/
update t1 set id = s1.nextval where id = -1
/
commit
/
select t1_hist.*
from t1_hist
order by t1_hist.ts, t1_hist.id
/
/*
* MAIN END
*/


Cleanup:


drop table t1_hist_ptr
/
drop table t1_hist
/
drop table t1
/
drop sequence s1
/
prompt "---------- END ----------"

Welcome!

I am a senior software engineer doing a lot of things (sometimes I even think too many at a time). Among these are Java application development and improvement, database development and maintenance as well as investigation of performance issues in Oracle databases.

In case you wonder what error ORA-0815 is - it does not exist as far as I know. The phrase "08/15" seems to be mostly used in German speaking countries. It originated from a machine gun model and means something like "ordinary" or "cheap". See the German Wikipedia for details.

In this blog I will try to present Oracle related stuff that I believe is interesting to others. The focus will be on SQL development and improvement and not so much about DBA topics. Please do not expect too much as my writing time resources are limited. If you find anything helpful I would be glad. Enjoy!

robert