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

Question

I'd 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?

Can you be more specific? What tables or data are you storing? SQL generally has a getdate() that retrieves the date and time. — Tyler Ferraro, Apr 21 '11 at 17:33
best way is to push it from you data-access code (like SubSonic do) or write triggers for update and insert — TheVillageIdiot, Apr 21 '11 at 17:35
http://stackoverflow.com/questions/5744067/sql-server-automatic-update-datetimestamp-field/5746102#5746102 — Mikael Eriksson, Apr 21 '11 at 19:29

score 3 · Answer 1 · answered Apr 21 '11 at 17:33

3

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.

answered Apr 21 '11 at 17:33

Rebecca Chernoff

21,115
5
41
45

Does anyone have an example of a trigger like that? – ScArcher2 Apr 21 '11 at 18:32

score 3 · Accepted Answer · answered Apr 21 '11 at 19:25

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

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

2 Answers2