In following example we will first create a sample table and later we will add a column which will be defaulted to the current date time when any new record is inserted. The only drawback of this method is that if there is any existing row in your table it will be automatically have the current date time when the column is created. Honestly I do not see any solution to this issue as this is related to design of the database. If you know what was the datetime when rows were created you can update those rows with those value otherwise, just have any values stored there.
Let us see our solution. Let us first create a table which does not have column with current datetime. In our case we will assume that there are only two rows in the table.
USE tempdb
GO-- Create TableCREATE TABLE TestTable (ID INT, Col1 VARCHAR(100));-- Insert ValuesINSERT INTO TestTable (ID, Col1)SELECT 1, 'First'UNION ALLSELECT 2, 'Second';-- Select from tableSELECT *FROM TestTable
GO
Now let us add a column to this table with default value as a current datetime. You will notice that the two rows which are inserted into the table have current datetime.
-- Add Column with Default Current Date TimeALTER TABLE TestTableADD DateInserted DATETIME NOT NULL DEFAULT (GETDATE());-- Select from tableSELECT *FROM TestTable
GO
As a third step let us enter the a new row. Make sure that you do not insert any value in the newly created column where you have default value as a current date time.
-- Now Insert New RowsINSERT INTO TestTable (ID, Col1)SELECT 3, 'Third';INSERT INTO TestTable (ID, Col1)SELECT 4, 'Fourth';GO-- Select from tableSELECT *FROM TestTable
GO
You will notice in the result set that the new column will contain current date time of the row created. This way you can get the value when the row was created.
Now you can clean up the resultset.
-- Clean upDROP TABLE TestTable
GO
Here is the question back to you – “It is simple to create a column where we have default daytime value to know when the row was created. Is there any way to know when the row was updated without explicitly updating any column with datetime?”
No comments:
Post a Comment