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?

[Webinar] Streamline your web hosting managementRegister Today

x
 
chapmandewConnect With a Mentor Commented:
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
 
Rajkumar GsConnect With a Mentor Software 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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
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
All Courses

From novice to tech pro — start learning today.