Insert Date
-
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
-
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 -
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] -
Thank you for the info.
-
Alternatively, you can define an INSERT trigger to set the insertdate.
-
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
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Y
eah, 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