Sometimes you need to add a CreatedDateTime and ModifiedDateTime columns to your table, here is an easy way to do it...

First create a test table:

Create Table Table_1

ID Int Primary Key Identity(1, 1),

Col1 Varchar(50),

CreatedDateTime DateTime Not Null Default Current_Timestamp,

ModifiedDateTime DateTime Null Default Null

Note that the default value for the column CreatedDateTime is Current_Timestamp.

To update the ModifiedDateTime column we need this trigger:

Create Trigger Table_1_Update On Table_1 After Update

Update Table_1 Set ModifiedDateTime = GetDate()

From Table_1 Inner Join Inserted

On Table_1.ID = Inserted.ID

Now Test:

Insert Into Table_1 (Col1) Values ('Hello');

Select * From Table_1;

Update Table_1 Set Col1 = 'Hi' Where ID = 1;

Select * From Table_1;