sql when set default value getdate(), does it set value when run update statement?

Question

I know when you insert a value into db, it set that column value as current datetime, does it apply to it when you run a update statement?

e.g.

table schema:

Id, Name, CreatedDate(getdate())

when i insert into table id = 1 , name = 'john' it will set createdDate = current date

if i run an update statement

update table set name="john2" where id =1

Will it update the createdDate?

Answer 1

No, a DEFAULT CONSTRAINT is only invoked on INSERT, and only when (a) combined with a NOT NULL constraint or (b) using DEFAULT VALUES. For an UPDATE, SQL Server is not going to look at your DEFAULT CONSTRAINT at all. Currently you need a trigger ( see How do I add a "last updated" column in a SQL Server 2008 R2 table? ), but there have been multiple requests for this functionality to be built in.

I've blogged about a way to trick SQL Server into doing this using temporal tables:

• Maintaining LastModified Without Triggers

But this is full of caveats and limitations and was really only making light of multiple other similar posts:

• A System-Maintained LastModifiedDate Column
• Tracking Row Changes With Temporal Columns
• How to add “created” and “updated” timestamps without triggers
• Need a datetime column that automatically updates

Answer 2

wow - hard to understand...

i think NO based on the clues.

if you insert a record with a NULL in a column, and that column has a default value defined, then the default value will be stored instead of null.

update will only update the columns specified in the statement.

UNLESS you have a trigger that does the special logic - in which case, you need to look at the trigger code to know the answer.