Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> Re: Does Oracle Have a Way to Store Historical Changes to Records?
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;
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
![]() |
![]() |