Question About DateCreated and DateModified Columns - MS SQL Server

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.

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

Open in new window

LVL 1
F-J-KAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chapmandewCommented:
put a trigger on the table to make sure the modified date is maintained when an update is made:


create trigger mytrigger on mytable
after update
as
begin
update t
set datemodified = getdate()
from mytable t join inserted i on t.customerid = i.customerid
end
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rajkumar GsSoftware EngineerCommented:
Check this code

Do you mean this ?
Raj
-- WHILE INSERT
INSERT INTO Customer (customerID, ... ,dateCreated, dateModified)
	VALUES (@CustomerID, ... , GETDATE(), GETDATE())

-- WHILE UPDATE....
UPDATE Customer SET SomeColumn = @SomeValue, dateModified=GETDATE()
	WHERE CustomerID = @@CustomerID

Open in new window

0
coolvivsCommented:
I don't understand why u putting dateModified   as default date when u already doing some action on it set the dateModified  as null and later pass the date when the records gets modified.

The purpose of dateModified  is when records modified  and dateCreated means records creation date
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

F-J-KAuthor Commented:
@chap...

can you please explain what "inserted i" is and why do we need it? I don't know much about triggers...
0
F-J-KAuthor Commented:
Thank you...
0
F-J-KAuthor Commented:
Discard my last question...

Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.