Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2005
    Posts
    19

    Unanswered: Updated Column Name

    Hi,

    Can anybody tell me how to find the column name(s) for recently updated table.

    Thanks,

    Kishore

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    could you explain a bit more?

    here's how you display column information:
    Code:
    select *
      from INFORMATION_SCHEMA.COLUMNS
     where TABLE_NAME = 'mytable'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2005
    Posts
    19
    I need to have a modification log table, which will carry the table name, column name, modified user name, old value (old data) and the updated timestamp for all updation that happens in the database. How can I achieve this?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you'd probably want to use triggers, one for each table in your database
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2005
    Posts
    19
    Is there any other way other than using triggers for each and every table? And will you pl. let me know how to find the column name and capture the old data from an updated table?

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You need to use triggers.

    Your triggers should reference virtual tables used by SQL Server during the transaction. The two tables are named "inserted" and "deleted", and have a structure identical to that of your production table. "inserted" contains all the records being inserted into your table. "deleted" contains all the records being deleted from your table. When an update occurs, the records is inserted and deleted, and thus will appear in both virtual tables giving you a "before and after" view of the transaction.

    In you trigger, you can select from or join the virtual tables just like any other table or view.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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