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
 General SQL Server Forums
 New to SQL Server Programming
 Timestamp
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ugh3012
Yak Posting Veteran

62 Posts

Posted - 04/07/2014 :  10:52:55  Show Profile  Reply with Quote


I know 2008 MS SQL Server has a timestamp data type that adds date and time when the rows are inserted. Is there a way to automatically update the date and time when the rows are updated?

ugh3012
Yak Posting Veteran

62 Posts

Posted - 04/07/2014 :  14:16:23  Show Profile  Reply with Quote
Could I use trigger event to do this?
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3649 Posts

Posted - 04/07/2014 :  16:07:22  Show Profile  Reply with Quote
The timestamp data type (which is deprecated in SQL 2008, so you should be using rowversion) is not really date and time. Also, when the rows are updated the rowversion gets updated without you having to do anything, so you don't need a trigger.

There is documentation here: http://technet.microsoft.com/en-us/library/ms182776(v=sql.100).aspx
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 04/08/2014 :  07:20:02  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Also if you want to have date and time values updated, you should use DATETIME datatype defaults to GETDATE() and use UPDATE Trigger if you want to modify this date whenever a column value is updated

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

ugh3012
Yak Posting Veteran

62 Posts

Posted - 04/08/2014 :  10:54:25  Show Profile  Reply with Quote
quote:
Originally posted by James K

The timestamp data type (which is deprecated in SQL 2008, so you should be using rowversion) is not really date and time. Also, when the rows are updated the rowversion gets updated without you having to do anything, so you don't need a trigger.

There is documentation here: http://technet.microsoft.com/en-us/library/ms182776(v=sql.100).aspx



I dont think rowversion will work because I need to compare dates to find the one that was updated after certain date.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3649 Posts

Posted - 04/08/2014 :  11:07:44  Show Profile  Reply with Quote
In that case, follow what Madhivanan suggested - i.e., add a new column for keeping track of updates/creates like shown below:
ALTER TABLE YourTable ADD YourTimestampCol DATETIME DEFAULT (GETDATE()) NOT NULL;
Then, add a trigger like this:
CREATE TRIGGER dbo.UpdateYourTableTrigger
ON YourTable FOR UPDATE
AS
	UPDATE YourTable SET YourTimeStampCol = GETDATE();
GO
While you are at it, depending on your requirements, you may also want to add an username column so you can keep track of who made the updates to the data.
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