Question About DateCreated and DateModified Columns - SQL Server

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

You might want to look at creting an update trigger to update this value for you

Have a look at something like

CREATE TABLE Vals(
        ID INT,
        Val VARCHAR(10),
        DateCreated DATETIME DEFAULT GetDate(),
        DateUpdated DATETIME DEFAULT GetDate()
)
GO

CREATE TRIGGER Upd ON Vals
AFTER UPDATE
AS 
UPDATE Vals
SET     DateUpdated = GetDate()
FROM    Vals INNER JOIN
        inserted ON Vals.ID = inserted.ID
Go

INSERT INTO Vals (ID, Val) SELECT 1, 'A'
SELECT *
FROM    Vals
GO

UPDATE Vals SET Val = 'B'
SELECT *
FROM    Vals
GO

DROP TABLE Vals
GO