Re: Does Oracle Have a Way to Store Historical Changes to Records?

From: <fitzjarrell_at_cox.net>
Date: 3 Aug 2005 12:13:43 -0700
Message-ID: <1123096423.358359.7860@z14g2000cwz.googlegroups.com>

GeoPappas wrote:
> I am sure it does, but I am not sure of its marketing name.
>
> Say you have a record that was created and now a user has edited the
> record. Does Oracle have a way of storing both the original record and
> modifications? Sort of like an audit trail or history of changes.
>
> For example, say you have a table with the following columns:
>
> DATA VARCHAR2(25)
> UPDATE_USER VARCHAR2(10)
> UPDATE_DATE DATE
>
> At first, user ABC creates a record with DATA='HELLO' on 05/01/2005
> 06:30a.
>
> Next, user DEF changes DATA='HELLO AGAIN' on 05/02/2005 07:00a.
>
> Next, user XYZ changes DATA='GOODBYE' on 05/31/2005 09:45a.
>
> Could Oracle save all of those points, so that I could go back and look
> at any point in time?

Certainly, and it's called a history table, populated by a trigger on the source table. One creates a copy of the source table, including transaction date, action and modifying user, then populates the table with the :old values on update and the :new values on insert (if you so desire). As you have designed your table to include the modifying user and the date such additions would be unnecessary. Given the table is named my_table (for want of a beter term) the copy could be named my_table_hist; the trigger would populate mytable_hist from the existing values from my_table on delete, and on the new values for insert or update:

SQL> desc my_table

 Name                                      Null?    Type
 ----------------------------------------- --------
----------------------
 DATA                                               VARCHAR2(25)
 UPDATE_USER                                        VARCHAR2(10)
 UPDATE_DATE                                        DATE

SQL> desc my_table_hist
 Name                                      Null?    Type
 ----------------------------------------- --------
----------------------
 DATA                                               VARCHAR2(25)
 UPDATE_USER                                        VARCHAR2(10)
 UPDATE_DATE                                        DATE
 ACTION                                             VARCHAR2(10)

create or replace trigger mytable_hist_trg before insert or update or delete on my_table for each row
begin

     if inserting then
     insert into my_table_hist (data, update_user, update_date, action)
     values (:new.data, :new.update_user, :new.update_date, 'INSERT');
     elsif updating then
     insert into my_table_hist (data, update_user, update_date, action)
     values (:new.data, :new.update_user, :new.update_date, 'UPDATE');
     elsif deleting then
     insert into my_table_hist (data, update_user, update_date, action)
     values (:old.data, :old.update_user, :old.update_date, 'DELETE');
     end if;

end;
/

SQL> insert into my_table values ('HELLO','ABC', sysdate -1) SQL> / 1 row created.

SQL> update my_table set data = 'HELLO AGAIN', update_user = 'DEF' where update_user = 'ABC';

1 row updated.

SQL> update my_table set data = 'GOODBYE',update_user = 'XYZ' where update_user = 'DEF';

1 row updated.

SQL> commit;

Commit complete.

SQL> select *
  2 from my_table;

DATA                      UPDATE_USE UPDATE_DA
------------------------- ---------- ---------
GOODBYE                   XYZ        03-AUG-05

SQL> select *
  2 from my_table_hist;

DATA                      UPDATE_USE UPDATE_DA ACTION
------------------------- ---------- --------- ----------
HELLO                     ABC        02-AUG-05 INSERT
HELLO AGAIN               DEF        03-AUG-05 UPDATE
GOODBYE                   XYZ        03-AUG-05 UPDATE

SQL> I hope this helps.

David Fitzjarrell Received on Wed Aug 03 2005 - 14:13:43 CDT