Display the latest updated data

As Ak

5y725

I have to display the data that currently being update. The problem is, I'm using the CONVERT function for the columns that i need to display.

This is my code for fetch the data with status 'New'. This code is before updating the status.

SELECT CONVERT(varchar(10), Start_RG, 2) AS Start_RG, CONVERT(varchar(10), End_RG, 2) AS End_RG FROM RG WHERE Status = 'New'

This is the code for updating the status to 'In Use'

UPDATE TOP (1) RG SET Status = 'In Use' OUTPUT deleted.Start_RG, deleted.End_RG, deleted.Status WHERE Status = 'New'

How can i fetch the Start_RG and End_RG that currently updated the status to In Use

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Kiran Mohanty5y ago
Accepted answer
Then you will have to define a trigger and this trigger should save last status change in a new table. All you have to do is, select from new table and inner join with current table. Below link may help you.

https://database.guide/create-a-last-modified-column-in-sql-server/#:~:text=Some%20database%20tables%20include%20a,trigger%20to%20perform%20this%20update.
Amit Gupta5y ago
You should consider by adding 'updatedDate' column ( which will be quite handy and useful in near future) and then you can easily use order by UpdateDate desc
Mageshwaran R5y ago
Hi AK,
please refer this link:
https://stackoverflow.com/questions/31634918/how-do-i-add-a-last-modified-and-created-column-in-a-sql-server-table
As Ak5y ago
@Kiran Mohanty No, I dont have LastUpdatedDate column.
Kiran Mohanty5y ago
Do you have LastUpdatedDate column ? If yes then you can do order by on LastUpdatedDate.