DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: insert column name into table??

  1. #1
    Join Date
    Oct 2001
    Posts
    7
    Hi. I want to create my own audit table and update this table with a trigger. I would like the audit table to capture the name of the column that was changed in the original table. I know how to do the new/old values, etc. but I am not sure how to capture the name of the changed column. for example (this is not the real table... real table has > 20 columns)

    original table:
    user_id (PK)
    user_name
    user_title
    street
    city
    zip
    phone

    audit table:
    user_id
    column_name
    old_val
    new_val
    create_dt
    create_by

    If user changes the user_title in the original table from 'Admin' to 'CEO' for user_id = 5, I want my audit table record to be:

    (5, 'user_title', 'Admin', 'CEO', sysdate, user)

    Does anybody know the syntax to find and insert the column_name such as 'user_title'? Is this even feasible/possible? Any help appreciated!!! Thanks.

  2. #2
    Join Date
    Jun 2001
    Location
    NJ
    Posts
    118
    HI Chrisr,
    Avoid using trigger.
    I assume that you do the changes from the application
    and would have a package/ Proc or function to change any user details. Use the same package/ Proc or function to insert into the audit table.
    You can get the old values before update statement by select statement
    or return as a parameter from the appln .

    If you want to use trigger.
    You can use :new and :Old in the before or after update
    :new will give the new value of user_title (:new.user_title :CEO)
    :Old will have the old value (:Old.user_title :Admin)

    I hope this is clear

    Thanks
    GD_1976

    [Edited by gd_1976 on 11-06-2001 at 05:04 PM]

  3. #3
    Join Date
    Oct 2001
    Posts
    7
    thanks gd_1976... good thought on using the procedure, I think I'll do that... however, I was aware of how to do :new, ld, but I cannot figure out how to get the name of the column being changed. for example, if they updated the 'user_title' column, I would want the text "user_title" along with old and new values. similarly, if they updated the 'zip' column, I would want the text "zip" along with old and new values... does this make sense?

  4. #4
    Join Date
    Jun 2001
    Location
    NJ
    Posts
    118
    HI Chris,
    I don't think you can find out what has changed? But you can always use the procedureand compare the old old and new values
    have an indicator
    For example,
    You have user_title as admin
    You are changing to CEO
    User id : chris (it is the unique identifier)
    Before updating, select the old values, compare it with the new values, if anything changed, change the corresponding indicator to changed like user_title_ind = true, qip_indicator,
    issue the update statement, check for the status, if successful,
    use the above indicator and insert the values.

    Hope this helps.

    GD_1976.

  5. #5
    Join Date
    Oct 2001
    Posts
    122
    Oracle's Designer has wonderful facility to generate Auditing and Journalling mechanism for the tables.

    It automatically generates and adds four audting columns, triggers to populate auditing coulmns, creates journal table based on original table, generates triggers to populate journal tables. It saves lots of developement time and effort.

    Hope this helps.

  6. #6
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    wow..... I know only one solution to your inquiry. And that's to use dynamic SQL.

    Dynamic SQL can be used when the fields itself of a table become the data that you want to manipulate. But this is a complex programming. I'll make a review of the materials I've done with this and I hope I can get back to you.

  7. #7
    Join Date
    Oct 2001
    Posts
    7
    thanks for all the suggestions! they have given me some ideas and things to try. If I get this to work, I'll post the results! And reydp, I'll check back to see if you were able to find anything. thank you!

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