Updates in audit table

Dev Shed Forums Sponsor:

May 22nd, 2011, 07:36 PM

shamit8

Registered User

Join Date: May 2011

Posts: 1

Time spent in forums: 29 m 6 sec
Reputation Power: 0

Updates in audit table

Hi,

I have to create a audit/history table on a master table so that I can store the old/current state of data in my audit table. I am planning to write following program --
1. Created the audit table with similar number of records.
2. Everyday at a particular time I will compare the audit/main table and push the records in audit table which are either updated or not present in the audit table so that the audit table = main table + old state of data.

I am unable to figure out the proper way to implement the point 2 above in oracle database.

Can someone please share his experience/ thoughts on the above problem.

Thanks a lot in advance.

Cheers!!
Amits

May 23rd, 2011, 01:17 PM

clivew

Contributing User

Join Date: Jan 2006

Location: Carlsbad, CA

Posts: 2,045

Time spent in forums: 1 Week 6 Days 2 h 37 m
Reputation Power: 382

I think you need an
after insert or update or delete trigger on the main table.

I suggest that the audit table has its own incrementing surrogate PK field plus a timestamp field to indicate the moment of insertion for sorting.

You will have to decide how to indicate deletion.
Insertion will simply be the earliest record containing the main table PK.

Last edited by clivew : May 23rd, 2011 at 01:19 PM. Reason: Added comments about insertion and deletion

May 23rd, 2011, 02:04 PM

shammat

Contributing User

Join Date: Oct 2003

Location: Germany

Posts: 2,685

Time spent in forums: 1 Month 3 Weeks 3 Days 19 h 57 m 33 sec
Reputation Power: 284

You don't need to code anything. Use a flashback archive on your main table and you'll be able to query the contents of your table at any point in the past.

Code:

SELECT *
FROM your_table
AS OF TIMESTAMP '2011-05-19 17:04:12'

May 23rd, 2011, 07:29 PM

clivew

Contributing User

Join Date: Jan 2006

Location: Carlsbad, CA

Posts: 2,045

Time spent in forums: 1 Week 6 Days 2 h 37 m
Reputation Power: 382

Shammat:

A couple of questions as I have not used this feature.
1. Is it actually available before version 11?
2. I see how it gives you a flashback snapshot of a table at various selected TIMESTAMPS
but how do you query for a history of changes to a record?
e.g. With an audit table I might want to track changes something like:

SQL Code:

Original - SQL Code
SELECT * FROM AUDIT_TABLE WHERE MAIN_PK_COL = :MAIN_PK_COL ORDER BY TIMESTAMP_COL

SELECT * FROM AUDIT_TABLE
WHERE MAIN_PK_COL = :MAIN_PK_COL
ORDER BY TIMESTAMP_COL

Of course actual change history queries might be more complex.

Clive

Updates in audit table

Developer Shed Advertisers and Affiliates