Getting DATETIME for rows inserted/Modified

Question

Hi There,

am using SQL server 2005 , i have a requirement to get the Creation datetime of all the rows in a particular table, unfortunately the table do not have any "rowverion" or datetime column ( i know this is a major design flaw).

so , i was wondering if SQL server maintains datetime for each row inserts.

comments suggestions appreciated

Regards Deepak

Answer 1

No, SQL Server does not timestamp the rows automatically when they are created. As you suggested, for the future, you may want to create a new date_created column and set it default to GETDATE():

ALTER TABLE       your_table  
  ADD CONSTRAINT  dc_your_table_date_created
  DEFAULT         GETDATE()
  FOR             date_created;

You may also use a trigger instead, if you prefer, as @Vash suggested in the other answer.

Answer 2

If this is a bussines business, purpose you should add the column to table and create a triger AFTER INSERT or UPDATE that set the sysdate to those rows. Or you can use the rowversion