Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2008
    Posts
    46

    Unanswered: How can I find the date and time of effected record?

    How can I find the date and time of inserted/edit/updat record on the table?

    eg.
    Use TestDb
    Go
    INSERT INTO tblA (ID,Name) VALUES(11,'aaa')


    Now i have to know the date and time...that record is effected on TestDb.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I think that the best way to do this is to add a column to record the "last update date/time", and if you can't depend on the code keeping that column correct then add a trigger to force it.

    -PatP

  3. #3
    Join Date
    Oct 2008
    Posts
    46
    No no i don't want to add column because third party software create new db /tables/view etc. I have more than 30-35 db and each db has more than 2-3 hunders tables and views. So just i have to track in which db has insert/edit/delete record....can we get thoes information from system database or system tables?

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Short answer: No.
    Long answer: Nope.
    If it's not practically useful, then it's practically useless.

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

  5. #5
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    If you are using SQL Server 2008 you could enable Change Data Capture (CDC) for the table. The sys.fn_cdc_map_lsn_to_time function will return a transaction time for any change.

    I don't think the same thing is possible using Change Tracking (CT) but I could be wrong because I'm more familiar with CDC than CT.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Short of adding a column or running a Profile trace, I can't think of any way to get the time a row was last modified. Operative word being MIGHT, you might be able to work something out using replication to another database and establishing a "heartbeat" if you were really looking for something to help occupy your spare hours for the lifetime of this application.

    -PatP

  7. #7
    Join Date
    Oct 2008
    Posts
    46

    Red face

    We have SQL SERVER 2005.

    Thanks guys.

Posting Permissions

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