Results 1 to 7 of 7
  1. #1
    Join Date
    May 2011
    Posts
    2

    Unanswered: 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
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    post DDL for both master table & audit table

    post SQL that properly identifies "the records in audit table which are either updated or not present in the audit table"

    alternatively consider writing a TRIGGER on master table
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    May 2011
    Posts
    2
    Hi,

    Thanks for your suggestion. I am having around 80 columns in the main tables and I dont know which column will be updated or how many column will be updated on that row. Can you please explain the best way to write post sql for this kind of scenario ??

    Cheers!!
    Amit Sh

  4. #4
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Simply use a "flashback archive". With that, you can query the old state of your table at any point in the past without any programming at all.

    The query would look something like this:
    Code:
    SELECT *
    FROM your_table
    AS OF TIMESTAMP '2011-05-19 17:04:12'
    and would return the precise content of the table at that date and time

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Can you please explain the best way to write post sql for this kind of scenario ??
    Since you don't answer my questions/requests, I do not have any details to answer in more completeness
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Flashback is only good as the space you set aside for it and how far back you go. It is NOT unlimited. I would implement auditing in one of two ways.

    1) A trigger on the table that would update the audit table on insert, delete , or update.

    2) Simply use Oracle Auditing for the table. It is built into the database already.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  7. #7
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by beilstwh View Post
    Flashback is only good as the space you set aside for it and how far back you go. It is NOT unlimited.
    I'm not talking about flashback.
    I'm talking about flashback archive which is unlimited

    Simply use Oracle Auditing for the table. It is built into the database already.
    I might be mistaken, but I though auditing will not show you "historical" values, only which user did the change. Can it also show the values a row had before/after the user changed it?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •