Getting Last modified record from table

Question

Getting Last modified record from table

MuhammadShafiq

SSCrazy

Points: 2509
More actions
November 30, 2011 at 4:09 am

#248747

Hi all,
IS it possible to get the id of last modified record from database table?
If some one insert a new record ,then i can get its it by MAX(id) but what if some one have modified the middle record?
so is it possible?

Viewing 15 posts - 1 through 15 (of 15 total)

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

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 1

You will have to maintain a seperate column(like ModifiedDate) and update it through a trigger. There is no other easy way to track it.

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

Actually, there's a great way to track it without a trigger if you can add a column as Kingston suggested. The difference is that you would use the TIMESTAMP datatype. The last row inserted or modified in the table will always have the highest timestamp and I mean without a trigger.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Phil Parkin SSC Guru Points: 246962 More actions · Answer 3

Jeff - that's a great idea.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 4

Jeff Moden (11/30/2011)
Actually, there's a great way to track it without a trigger if you can add a column as Kingston suggested. The difference is that you would use the TIMESTAMP datatype. The last row inserted or modified in the table will always have the highest timestamp and I mean without a trigger.

I have never heard about this idea ever. Great idea, Jeff. Learnt a new thing today for a common problem people come up with.

Now I will think more than a couple of times before using an absolute statement like There is no other easy way to track it.🙂

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

Jeff Moden (11/30/2011)
Actually, there's a great way to track it without a trigger if you can add a column as Kingston suggested. The difference is that you would use the TIMESTAMP datatype. The last row inserted or modified in the table will always have the highest timestamp and I mean without a trigger.

WOW. Now there is an actual reason to use this otherwise nearly useless datatype. 😛 Great idea!!!

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 6

Sean Lange (11/30/2011)
Jeff Moden (11/30/2011)
Actually, there's a great way to track it without a trigger if you can add a column as Kingston suggested. The difference is that you would use the TIMESTAMP datatype. The last row inserted or modified in the table will always have the highest timestamp and I mean without a trigger.
WOW. Now there is an actual reason to use this otherwise nearly useless datatype. 😛 Great idea!!!

Just to add what Jeff has already said.

Per MSDN:

timestamp is the synonym for the rowversion data type and is subject to the behavior of data type synonyms. In DDL statements, use rowversion instead of timestamp wherever possible.

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 7

Sean Lange (11/30/2011)
Jeff Moden (11/30/2011)
Actually, there's a great way to track it without a trigger if you can add a column as Kingston suggested. The difference is that you would use the TIMESTAMP datatype. The last row inserted or modified in the table will always have the highest timestamp and I mean without a trigger.
WOW. Now there is an actual reason to use this otherwise nearly useless datatype. 😛 Great idea!!!

The main use for the ROWVERSION (TIMESTAMP) datatype is for an application to be able to detect and prevent concurrent updates by different users that would overwrite each others changes.

Sean Lange SSC Guru Points: 286590 More actions · Answer 8

Michael Valentine Jones (11/30/2011)
Sean Lange (11/30/2011)
Jeff Moden (11/30/2011)
Actually, there's a great way to track it without a trigger if you can add a column as Kingston suggested. The difference is that you would use the TIMESTAMP datatype. The last row inserted or modified in the table will always have the highest timestamp and I mean without a trigger.
WOW. Now there is an actual reason to use this otherwise nearly useless datatype. 😛 Great idea!!!
The main use for the ROWVERSION (TIMESTAMP) datatype is for an application to be able to detect and prevent concurrent updates by different users that would overwrite each others changes.

I know the usage of the type but it is just isn't anything I need to deal with. I don't have anything with a large number of users all trying to edit the same data. I have never had a reason to use it.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 9

Now all we need to know is the true reason why someone would want to do this especially since, as Michael Valentine Jones pointed out, the last row updated could change in a heartbeat. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

MuhammadShafiq SSCrazy Points: 2509 More actions · Answer 10

Jeff Moden (11/30/2011)
Actually, there's a great way to track it without a trigger if you can add a column as Kingston suggested. The difference is that you would use the TIMESTAMP datatype. The last row inserted or modified in the table will always have the highest timestamp and I mean without a trigger.

This is good idea but i want to clear one thing.

before this idea i was using like this(i know this was not good to get edited data)

SELECT @RecordMAX=MAX(ID) FROM ABC

UPDATE ABCSET x= @x WHERE ID=@RecordMAX

(ID is primery key)

Now i m using TIMESTAMP so my code is here

SELECT @RecordMAX=MAX(TrackActivity) FROM ABC

UPDATE ABC SET x= @x WHERE TrackActivity=@RecordMAX

i am not sure that TrackActivity can be use a primery key?is this unique?i have doubt not to update wrong data.any help?

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 11

Engr Shafiq (2/20/2012)
Jeff Moden (11/30/2011)
Actually, there's a great way to track it without a trigger if you can add a column as Kingston suggested. The difference is that you would use the TIMESTAMP datatype. The last row inserted or modified in the table will always have the highest timestamp and I mean without a trigger.
This is good idea but i want to clear one thing.
before this idea i was using like this(i know this was not good to get edited data)
SELECT @RecordMAX=MAX(ID) FROM ABC
UPDATE ABCSET x= @x WHERE ID=@RecordMAX
(ID is primery key)
Now i m using TIMESTAMP so my code is here
SELECT @RecordMAX=MAX(TrackActivity) FROM ABC
UPDATE ABC SET x= @x WHERE TrackActivity=@RecordMAX
i am not sure that TrackActivity can be use a primery key?is this unique?i have doubt not to update wrong data.any help?

This is a classic concurrency problem. I don't think your code is not proper without some locking hints or serialiazable transaction isolation level (and enclosing transaction) due to there being the potential for an insert or delete between your SELECT and UPDATE.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

MuhammadShafiq SSCrazy Points: 2509 More actions · Answer 12

TheSQLGuru (2/21/2012)
Engr Shafiq (2/20/2012)
Jeff Moden (11/30/2011)
Actually, there's a great way to track it without a trigger if you can add a column as Kingston suggested. The difference is that you would use the TIMESTAMP datatype. The last row inserted or modified in the table will always have the highest timestamp and I mean without a trigger.
This is good idea but i want to clear one thing.
before this idea i was using like this(i know this was not good to get edited data)
SELECT @RecordMAX=MAX(ID) FROM ABC
UPDATE ABCSET x= @x WHERE ID=@RecordMAX
(ID is primery key)
Now i m using TIMESTAMP so my code is here
SELECT @RecordMAX=MAX(TrackActivity) FROM ABC
UPDATE ABC SET x= @x WHERE TrackActivity=@RecordMAX
i am not sure that TrackActivity can be use a primery key?is this unique?i have doubt not to update wrong data.any help?
This is a classic concurrency problem. I don't think your code is not proper without some locking hints or serialiazable transaction isolation level (and enclosing transaction) due to there being the potential for an insert or delete between your SELECT and UPDATE.

Do we have any solution on sqlserver side to solve concurrency problems?it will be better to have on sqlserver side.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 13

This particular one is easy:

UPDATE ABCSET x= @x

WHERE ID= (SELECT MAX(ID) FROM ABC)

There are MUCH more difficult scenarios where concurrency will get you. Note this example assumes that @x comes from some different table than ABC and also that there is no concurrency issue with the "acquisition" of @x.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

MuhammadShafiq SSCrazy Points: 2509 More actions · Answer 14

@TheSQLGuru:

But there is issue like when some one is updating middle record?for example max id is 10 but i want to edit id=5 then this query l not work?why we are using timestamp is for this....

Actually i want to clear you about my question

i am saving or updating records first time and then updating image field after this.both operations are not cary in one query...