Question About DateCreated and DateModified Column

Question

CREATE TABLE Customer
(
        customerID         int identity (500,20) CONSTRAINT 
        .
        .
        dateCreated    datetime DEFAULT GetDate() NOT NULL,
        dateModified   datetime DEFAULT GetDate() NOT NULL
);

When i insert a record, dateCreated and dateModified gets set to default date/time. When i update/modify the record, dateModified and dateCreated remains as is? What should i do?

Obviously, i need to dateCreated value to remain as was inserted the first time and dateModified keeps changing when a change/modification occurs in the record fields.

In other words, can you please write a sample quick trigger? I don't know much yet...

Answer 1

UPDATE
    Customer
SET
    ... = NewValue,
    dateModified = DEFAULT
WHERE
    ...

I'd use this rather than dateModified = GETDATE() so GETDATE() is only used once (say you want to change to GETUTCDATE() in future)

Or a trigger if you have multiple update paths...?

Answer 2

@Kronass, you don't have any idea about what uou are saying!

timestamp is the synonym for the rowversion data type and is subject to the behavior of data type synonyms. In DDL statements, use rowversion instead of timestamp wherever possible. For more information, see Data Type Synonyms (Transact-SQL).

The Transact-SQL timestamp data type is different from the timestamp data type defined in the ISO standard.

The timestamp syntax is deprecated. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

rowversion (Transact-SQL) Is a data type that exposes automatically generated, unique binary numbers within a database. rowversion is generally used as a mechanism for version-stamping table rows. The storage size is 8 bytes. The rowversion data type is just an incrementing number and does not preserve a date or a time. To record a date or time, use a datetime2 data type.

Answer 3

@astander is right, you should just use an update trigger if you want this automated. My update triggers are slightly different (I use the 'inserted' virtual table). Here's one that should fit your schema (rename however you see fit):

CREATE TRIGGER [CustomerDateModifiedTrigger] ON [dbo].[Customer] 
FOR UPDATE
AS
UPDATE Customer
SET dateModified = GETDATE()
FROM Customer c
INNER JOIN inserted i ON c.customerID = i.customerID

Answer 4

There is a datatype in SQL Server Called timestamp. which keep track of the row version for each time you modify the row. Or if you want you can use a trigger and change the ModifiedDate Column.

Answer 5

1) Be sure to create the index for the primary key. (I just uncovered a mistake of this type recently.)

2) You can use one INSERT/UPDATE trigger instead of separate triggers at the price of a tiny loss of efficiency. If insert.DateCreated is null, then update Vals.DateCreated, otherwise update Vals.DateModified.

Answer 6

When i insert a record, dateCreated and dateModified gets set to default date/time. When i update/modify the record, dateModified and dateCreated remains as is? What should i do?

A Column default is only used when INSERTing and not by an UPDATE. The default will be used by the INSERT command if you do not supply the column or issue the DEFAULT keyword in the INSERT.

INSERT INTO Customer (col1, col2) 
VALUES (..,..)  ---get default for dateCreated & dateModified   

INSERT INTO Customer (col1, col2,dateCreated) 
VALUES (..,..,DEFAULT)  ---get default for dateCreated & dateModified   

INSERT INTO Customer (col1, col2,dateCreated,dateModified) 
VALUES (..,..,DEFAULT,DEFAULT)  ---get default for dateCreated & dateModified   

INSERT INTO Customer (col1, col2,dateCreated,dateModified) 
VALUES (..,..,'1/1/2010',DEFAULT)  ---only get default for dateModified   

INSERT INTO Customer (col1, col2,dateCreated,) 
VALUES (..,..,'1/1/2010')  ---only get default for dateModified   

INSERT INTO Customer (col1, col2,dateCreated,dateModified)
VALUES (..,..,'1/1/2010','1/2/2010')  ---no defaults for dateCreated & dateModifie

I like using a local variable set that the top of the procedure:

DECLARE @RunDate datetime
SET @RunDate=GETDATE()

I then use that within the procedure, so all changes (even on multiple tables) have the exact same date to the millisecond. I also prefer the dateModified column to allow nulls and not have a default, when it is inserted, it has been created not modified, I'll set the dateModified when it is actually modified.

then use:

UPDATE Customer
    SET importantColumn=
       ,dateModified = @RunDate
    WHERE ...

UPDATE CustomerPrice
    SET importantColumn=
       ,dateModified = @RunDate
    WHERE ...