How do I create and set fields for insert_date and last_modified_date in sql server 2008?

I'd开发者_JS百科 like to record the insert date and an update date on a table. What is the best way to do this in SQL Server 2008?

For Insert Date you can use the following trigger:

   CREATE TRIGGER INSERT_DATE ON TABLE1  
   FOR INSERT
    AS

    BEGIN

        SET NOCOUNT ON

        UPDATE TABLE1
        SET  CreatedOn = GETDATE()
                FROM TABLE1 A
        INNER JOIN Inserted INS ON (INS.Id = A.Id)

        SET NOCOUNT OFF

    END

and for Update you can use the following trigger:

CREATE TRIGGER Update ON TABLE1
FOR UPDATE
AS

BEGIN

    SET NOCOUNT ON

    UPDATE  TABLE1
    SET   UpdatedOn = GETDATE()
        FROM    TABLE1 A
            INNER JOIN Inserted INS ON (A.Id = INS.Id)

    SET NOCOUNT OFF

END

For the insert date column, you can set the column default to GETDATE() (or GETUTCDATE()).

For the update date, you would need to use a trigger to set the column to the current date whenever there's an update.