SQL Server Performance

Is it possible to put a GetDate() by default in a field without triggers?

Discussion in 'SQL Server 2005 General Developer Questions' started by EMoscosoCam, Nov 24, 2010.

  1. EMoscosoCam Member

    I have a table called MyTable with a field named UpdateTimeStamp (a datetime). I would like to know if it is possible that whenever there is a successful INSERT in that table, that the UpdateTimeStamp automatically gets the Date and Time of the operation, without using triggers.
    Thanks a lot.
  2. Adriaan New Member

    Yes, by adding a DEFAULT constraint to the column.
    Note that the default will only be applied if your insert query does not touch the column in question, so if your column has the default constraint:
    INSERT INTO MyTable (Col1) VALUES (1) -- will insert the current date into UpdateTimeStamp
    INSERT INTO MyTable (Col1, UpdateTimeStamp) VALUES (1, Null) -- will not insert the current date into UpdateTimeStamp
    Also, going by your column name, I assume you want it to auto-update when an existing row is updated. Well, for that you really do need a trigger, unless you can handle it in the update process.
  3. EMoscosoCam Member

    Thanks a lot for your reply. As suggested, I used the following sentenced and it worked nicely:ALTER TABLE MyTable ADD CONSTRAINT DF_MyTable_UpdateTimeStamp DEFAULT GETDATE() FOR UpdateTimeStamp
    I was trying to avoid to create a trigger for INSERTs and use only one for UPDATEs

Share This Page