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
 General SQL Server Forums
 New to SQL Server Programming
 Timestamp

Author  Topic 

ugh3012
Yak Posting Veteran

62 Posts

Posted - 2014-04-07 : 10:52:55


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 - 2014-04-07 : 14:16:23
Could I use trigger event to do this?
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-04-07 : 16:07:22
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

22864 Posts

Posted - 2014-04-08 : 07:20:02
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 - 2014-04-08 : 10:54:25
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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-04-08 : 11:07:44
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
   

- Advertisement -