How do I get the Last Inserted / Modifed Record

Question

Post reply

How do I get the Last Inserted / Modifed Record

go.kulabharathirgb

SSC Enthusiast

Points: 122
More actions
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]

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

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

BravehearT1326 SSChasing Mays Points: 616 More actions · Answer 1

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

LutzM SSC Guru Points: 107049 More actions · Answer 2

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]

go.kulabharathirgb SSC Enthusiast Points: 122 More actions · Answer 3

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

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