#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2011
    Posts
    1
    Rep 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
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,057
    Rep Power
    383
    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 02:19 PM. Reason: Added comments about insertion and deletion
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,788
    Rep Power
    349
    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'
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,057
    Rep Power
    383
    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:
    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

IMN logo majestic logo threadwatch logo seochat tools logo