SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 how to get last modified record from a table?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kirangentlebreeze1987
Starting Member

India
14 Posts

Posted - 04/06/2010 :  04:03:10  Show Profile  Reply with Quote
how to get last modified record from a table

kkk

webfred
Flowing Fount of Yak Knowledge

Germany
8760 Posts

Posted - 04/06/2010 :  04:04:20  Show Profile  Visit webfred's Homepage  Reply with Quote
...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.
Go to Top of Page

kirangentlebreeze1987
Starting Member

India
14 Posts

Posted - 04/06/2010 :  04:12:28  Show Profile  Reply with Quote
sorry,can you tell me what are the possible ways to get this task done?

kkk
Go to Top of Page

vaibhavktiwari83
Aged Yak Warrior

India
840 Posts

Posted - 04/06/2010 :  04:55:53  Show Profile  Reply with Quote
Create a column LastModifiedDate in your table and at the time of insert use Getdate() as value for that column.

Vaibhav T
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22744 Posts

Posted - 04/06/2010 :  05:14:00  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 04/06/2010 :  05:27:33  Show Profile  Reply with Quote
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/

Go to Top of Page

kirangentlebreeze1987
Starting Member

India
14 Posts

Posted - 04/06/2010 :  12:46:00  Show Profile  Reply with Quote
sorry,i am new to sqlserver
can you explain me in a clear way?
thanks


kkk
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 04/06/2010 :  12:54:12  Show Profile  Reply with Quote
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/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000