Best Way to Log Transactions?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Best Way to Log Transactions?

  1. #1
    Join Date
    Dec 2000
    Posts
    41

    Post

    I have an Oracle 8i database, and I want to add transaction logging. I know I can do this with triggers, but I'm not quite sure how.

    I read on a web page that in 8i, you can create triggers that fire when any database object is altered, rather than having to create triggers for ALL the database tables for doing transaction logging. However, I'm yet to find instructions for doing so. The article I saw this at was this link:
    http://www.inquiry.com/techtips/orac.../10min1100.asp

    So how do people do transaction logging in their 8i databases? I want something better than audit trails. I want to know what rows changed, what the change was, and who made it.

    Thanks.

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    Basically, you want to create a trigger that fires BEFORE INSERT/UPDATE/DELETE and saves your values to a table. At one client, we wanted to audit the values of the XYZ table. We created a table called XYZ_AUDIT that had the exact same columns as XYZ with the addition of USERNAME, AUDIT_DATE, AUDIT_ACTION. The AUDIT_ACTION was 'INSERT' for an insert statement, 'OLD' for the old row in an UPDATE statement, 'NEW' for the updated row in an UPDATE statement, and 'DELETE' for a deleted row.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    As a matter of fact, check out http://www.dbasupport.com/forums/sho...?threadid=6135 for the code.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  4. #4
    Join Date
    Dec 2000
    Posts
    41

    apply to all tables?

    Thanks for the info- that is extremely helpful.

    I do have one additional question: Does anyone know how one might apply a trigger to all the tables in the database? I would obviously have to alter that trigger code and make it more generic, but I'm wondering if this could be done.

    Can 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
  •  



Click Here to Expand Forum to Full Width