Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803

    Unanswered: Tracking database changes??

    I have seen in other databases where there is a tracking table where all deleted and updated data goes from other tables.

    For example, an update to table A would place the data that was there before the update into table B.

    If I wanted to use the same thing how would I go about that?

    Thanks.

    Frank

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    audit trails like this are usually implemented using database triggers.

    In PostgreSQL, you would write a trigger function, which would compare the new record values to the old record values. If the values are different, the function inserts a record into the audit table, with the timestamp, the old and new values, and the username.

    Then, a trigger is added, running against the table in question - when an update , insert, or delete comes along, the trigger(s) invoke the trigger function to audit the vhange.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  3. #3
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Thanks for the reply Loquin. I will see what info I can gather on the functions. I heve never used audit tables or triggers before.

  4. #4
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    the pg documentation has a decent explanation of triggers and trigger functions. In fact, one example is setting up audit trail trigger functions.
    Last edited by loquin; 04-13-07 at 15:42.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Posting Permissions

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