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.
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? |
|
|
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 |
|
|
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 updatedMadhivananFailing to plan is Planning to fail |
|
|
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. |
|
|
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.UpdateYourTableTriggerON YourTable FOR UPDATEAS 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. |
|
|
|
|
|
|
|