Post reply

How do I get the Last Inserted / Modifed Record

  • August 20, 2010 at 6:49 am

    #226096

    [font="Verdana"]Hi,

    How do I get the last inserted and modified record in SQL Server 2005?

    For example, I have modified the 5th record in Table1, insert 10th record in Table2, modified 8th record in Table3.

    I want to know last modified / inserted record in each table

    If I queried with Table1 will display 5th record has modified, Table2 will display 10th record has inserted and, Table3 will display 8th record has modified.

    Regards,

    Gokula[/font]

  • August 20, 2010 at 6:58 am

    #1209629

    Do you have any Triggers on the table in question for the inserts / update ?

  • August 20, 2010 at 7:07 am

    #1209636

    First thing: there is no such thing like the 5th row.

    The order of rows in a table doesn't really matter. Not even the physical order of rows really is deterministc (but that's a different story...).

    You need to identify a row based on values in one or more column(s) that will uniquely identify this row. Usually a primary key is used to ensure unique data.

    Let's assume we have a table with a column named SampleID and a primary key on this column.

    Your question then needs to be rephrased as "I have modified the row with SampleID=5."

    But what is your expected result for a statement like

    UPDATE MyTable SET SomeColumn = SomeColumn + 1 WHERE SampleID < 20

    There is no such thing like a single row to be the last one modified. The same for INSERT.

    If you need to know the rows last inserted you could add a DATETIME column [Inserted] with a default value = getdate().

    To get the time a row has been modified you could add another DATETIME column [Modified] and use an INSTEAD OF UPDATE trigger with the new column added.

    Or you could search this site for "SQL 2005 Audit trigger" to get some more ideas.


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • August 20, 2010 at 7:45 am

    #1209657

    Hai all,

    Thanks for your reply.

    Sorry for the inconvenience.

    5th record in the sense Employee number 5 if the table1 is Employee table.

    10th record in the sense Department number 10 if the table2 is department table. And so on.

    Regards,

    Gokula

  • August 20, 2010 at 8:24 am

    #1209695

    If you need to get the last inserted/modified, then you need a datetime column to store modification dates and a trigger to update them (or a rowversion column)

    Then you can simply query for the row with the latest modification date.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply