Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2004

    Question Unanswered: [Oracle] Auditing Table Changes

    Hi folks,

    I need an example on how to audit changes made on a table (insert, update, delete) without knowing the exact table structure.

    So I must store the user name, the old and new value and the date/time.

    The table structure can be read from a table called KDM_TBLS.

    So what I wanted now is to get the column names dynamically in a trigger (this is not possible, i know!!!)

    Now I need a way to get these Information with auditing.

    But how do I do this ?

    Thanks a lot.


  2. #2
    Join Date
    Sep 2003
    Virginia, USA
    I don't think you can do that. Many have tried and failed. Not even Oracle offers products with such a dynamic solution, although they'd like to in several products including Replication.

    For a solution that is nearly dynamic, and proven to work, look at Oracle's advanced replication software. When you tell it to generate rep support for a table, Oracle determines all of the columns and then builds/replaces a trigger with that data. If you add/mod/del a column then you must regenerate replication support. This is the same requirement for your auditing. You need code that can look at the table definitions and build a trigger, and you need to rerun this code each time you alter a table.

    Pitfall ... you need to quiesce the database before altering any tables. this is to prevent users from insert data into the new table while the triggers are based on the old table structure (and therefore invalid.)
    Author, Oracle Database 10g: From Nuts to Soup

Posting Permissions

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