CODEWITHSUNDEEP
sqlt-sql
Kiddo
Kiddo

Reputation: 5082

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

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?

Upvotes: 2

Views: 12925

Answers (4)

Aaron Bertrand
Aaron Bertrand

Reputation: 280490

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:

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

Upvotes: 8

Shakti Singh Chauhan
Shakti Singh Chauhan

Reputation: 594

You can achieve this using DEFAULT constraint like i did it with OrderDate field in below statement.

CREATE TABLE Orders ( O_Id int NOT NULL, OrderNo int NOT NULL, P_Id int, OrderDate date DEFAULT GETDATE() )

Upvotes: 0

Diego
Diego

Reputation: 36166

if your update statement tell to update a column with getfate() it will, but if you just update a name for example and you have a createdate column (which was inserted with getdate()), this columns wont be affected.

Upvotes: 1

Randy
Randy

Reputation: 16673

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.

Upvotes: 4

Related Questions