CODEWITHSUNDEEP
sqlsql-server
nagiah s
nagiah s

Reputation: 121

Set default date on updating a row in SQL Server table

I have a SQL Server table with ID, City, CreateDate, UpdateDate columns. Whenever a new row is created, default value is added(this is working as expected). Just like default value for new row, how to set default date when row is updated ie if I update the table(as shown below), UpdateDate should have default value GetDate().

CREATE TABLE TestTable 
(
     ID Int, 
     City Varchar(100), 
     CreateDate Datetime, 
     UpdateDate Datetime
);

ALTER TABLE TestTable 
    ADD CONSTRAINT [Constraint_CreateDate] DEFAULT (GETDATE()) FOR [CreateDate];

INSERT INTO TestTable (ID, City) 
VALUES (1, 'DC');

UPDATE TestTable 
SET City = 'NY' 
WHERE ID = 1; -- How to populate the UpdateDate when this update happen.

Upvotes: 0

Views: 4724

Answers (2)

Esperento57
Esperento57

Reputation: 17462

You can use mixed defaults with a trigger. But the cheapest solution in terms of performance is that of @marc_s.

Solution with trigger:

CREATE TABLE TestTable 
(
     ID Int, 
     City Varchar(100), 
     CreateDate Datetime default getdate(), 
     UpdateDate Datetime default getdate()
);

CREATE TRIGGER dbo.update_TestTable ON TestTable
AFTER UPDATE 
AS UPDATE TestTable SET UpdateDate = getdate()
   FROM TestTable t INNER JOIN inserted i ON t.ID = i.ID
;

INSERT INTO TestTable (ID, City) 
VALUES (1, 'DC');

select * from TestTable ;

UPDATE TestTable 
SET City = 'NY' 
WHERE ID = 1;

select * from TestTable ;

If you want test on line : test here

Upvotes: 1

marc_s
marc_s

Reputation: 754408

The DEFAULT constraint only works when inserting a new row, and there's no declarative way to define a default for an update. You'll just have to do this explicitly in your UPDATE statement:

UPDATE TestTable 
SET City = 'NY',
    UpdateDate = SYSDATETIME()
WHERE ID = 1;

Upvotes: 1

Related Questions