May 11, 2005 at 8:23 am
Hello:
I have a table that i want to add a field, say, Insertdate, that would only be populated whenever a new record was inserted, not for updates. I can easily add the field using Enterprise Manager, and define it as timestamp. But how do I ensure it will only be populated on INSERTS only ?
Thanks
May 11, 2005 at 8:28 am
Well if you want set a default date GETDATE() for the column at the time of definition, which will automatically populate with the system date on Insert, irrespective of whether you are referencing it in your insert or update statements.
If you are using stored procedures for Insert and Update , don't just include the column in your update statement, I am not sure If I am missing anything here.
Thanks
Prasad Bhogadi
www.inforaise.com
May 11, 2005 at 8:45 am
Alternatively you can use this:
alter table yourtable
add InsertDate datetime default getdate()
go
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
May 11, 2005 at 8:46 am
Thank you for the info.
May 12, 2005 at 3:58 pm
Alternatively, you can define an INSERT trigger to set the insertdate.
May 12, 2005 at 7:18 pm
Miller Time,
Don't define the new column as a TimeStamp datatype... the TimeStamp data type in MS-SQL Server is actually a binary number used for row versioning and has nothing to do with dates or times.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 13, 2005 at 6:00 am
Yeah, I defined it as a datetime field. Works great ! Only gets populated with an Insert, never modified after that. Thanks all for the good help !
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply