how to get last modified record from a table? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

how to get last modified record from a table?

Author

Topic

kirangentlebreeze1987
Starting Member

14 Posts

Posted - 2010-04-06 : 04:03:10

how to get last modified record from a table

kkk

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-06 : 04:04:20

...by using a column with modified date an time?

No, you're never too old to Yak'n'Roll if you're too young to die.

kirangentlebreeze1987
Starting Member

14 Posts

Posted - 2010-04-06 : 04:12:28

sorry,can you tell me what are the possible ways to get this task done?

kkk

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-04-06 : 04:55:53

Create a column LastModifiedDate in your table and at the time of insert use Getdate() as value for that column.

Vaibhav T

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-06 : 05:14:00

quote:
Originally posted by vaibhavktiwari83

Create a column LastModifiedDate in your table and at the time of insert use Getdate() as value for that column.

Vaibhav T

Better option is to create that column with default value of getdate()

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-06 : 05:27:33

quote:
Originally posted by madhivanan

quote:
Originally posted by vaibhavktiwari83

Create a column LastModifiedDate in your table and at the time of insert use Getdate() as value for that column.

Vaibhav T

Better option is to create that column with default value of getdate()

Madhivanan

Failing to plan is Planning to fail

And for update write an audit trail trigger to update this field with getdate() if not explicitly passed

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kirangentlebreeze1987
Starting Member

14 Posts

Posted - 2010-04-06 : 12:46:00

sorry,i am new to sqlserver
can you explain me in a clear way?
thanks

kkk

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-06 : 12:54:12

quote:
Originally posted by kirangentlebreeze1987

sorry,i am new to sqlserver
can you explain me in a clear way?
thanks

kkk

--add a new column with default getdate
ALTER TABLE yourTable ADD LastModifiedDate datetime NOT NULL DEFAULT GETDATE()

--then create audit trail trigger for updates


CREATE TRIGGER AuditTrail_YourTable
ON YourTable
AS
BEGIN
IF NOT UPDATE(LastModifiedDate)
 UPDATE y
 SET y.LastModifiedDate=DEFAULT
 FROM YourTable y
 INNER JOIN INSERTED i
 ON i.PK=y.PK
END

now you're done. once creation of above are over, its just a matter of doing


SELECT TOP 1 * FROM Table ORDER BY LastModifiedDate DESC

to get last modified record

and in case of batch updates use


SELECT TOP 1 WITH TIES * FROM Table ORDER BY LastModifiedDate DESC

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources