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 are set to the default date / time. When I update / modify a record, do dateModified and dateCreated stay as they are? What should I do?

Obviously, I need to assign the dateCreated value that was inserted the first time, and dateModified continues to change when changes / modifications occur in the record fields.

In other words, can you write a quick launch sample? I do not know yet...

Answer 1

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

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 

Answer 2

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

I would use this, not dateModified = GETDATE() , so GETDATE () is used only once (let's say you want to switch to GETUTCDATE () in the future)

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

Answer 3

When I insert a record, dateCreated and dateModified gets the default date / time. When I update / modify record, does dateModified and dateCreated remain as it is? What should I do?

The default value for a column is used only in INSERT ing, not UPDATE . The default value will be used by the INSERT command if you do not specify a column or run the DEFAULT keyword in 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 to use the local set of variables, which is located at the top of the procedure:

 DECLARE @RunDate datetime SET @RunDate=GETDATE() 

Then I use this in the procedure, so all changes (even on several tables) have exactly the same date for the millisecond. I also prefer the dateModified column to allow null and not have a default value when it is inserted, it was created not changed, I will set dateModified when it is actually changed.

then use:

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

Answer 4

@astander is right, you just need to use the update trigger if you want to automate. My update triggers are slightly different (I use a “pasted” virtual table). Here's the one that should fit your circuit (rename as 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 5

@Kronass, you have no idea what you are saying!

timestamp is a synonym for rowversion data type and depends on the behavior of data type synonyms. For DDL operations, use rowversion, if possible, rather than a timestamp. 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 out of date. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new developments and plan to change applications that currently use this feature.

rowversion (Transact-SQL) Is a data type that provides automatically generated unique binary numbers in a database. rowversion is commonly used as a mechanism for punching table rows. Storage size is 8 bytes. The rowversion data type is simply an increasing number and does not store a date or time. To write a date or time, use the datetime2 data type.

Answer 6

1) Be sure to create an index for the primary key. (Recently, I recently discovered an error of this type.)

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

Answer 7

In SQL Server Called timestamp, a data type exists. which keep track of the version of the line every time you change the line. Or, if you want, you can use a trigger and change the ModifiedDate column.