CODEWITHSUNDEEP
sql-server
K.P.
K.P.

Reputation: 105

Is there any way to update the modified date time automatically in SQL Server?

Is there any way to update the modified date time automatically in SQL Server.

I do not want to use Triggers. Also I want to avoid providing the value through application while calling SQL query.

Is there any support in SQL or in Dapper etc.

Upvotes: 0

Views: 1175

Answers (2)

K.P.
K.P.

Reputation: 105

I am able to solve the problem using Temporal Table. I am not sure is this a elegant solution. Here is how i solved.

Create Table:

CREATE TABLE extable4 (PriKey int PRIMARY KEY, ColValue varchar(200)
, [ModifiedDateTime] datetime2 (2) GENERATED ALWAYS AS ROW START  
, [ModifiedExpiryDateTime] datetime2 (2) GENERATED ALWAYS AS ROW END HIDDEN 
, PERIOD FOR SYSTEM_TIME (ModifiedDateTime,[ModifiedExpiryDateTime])  
 ) ;

Insert a record with out providing input to ModifiedDatetime.

insert into extable4(PriKey,ColValue) values(1,'Ver 1');

ModifiedDateTime Populated with systime.

update extable4 set ColValue='Ver 1.1' where PriKey=1;

ModifiedDateTime updated now. :)

Upvotes: 0

Ehsan K. harchegani
Ehsan K. harchegani

Reputation: 3128

If you want to keep track of the changes in database you can use a feature called

System-Versioned Temporal Table as explained here.

Using a Temporal Table, you will be able to query the recent state of the row as usual, in addition to the ability to query the full history of that row

It's very handy if you are interested in keeping a history of data changes

Upvotes: 2

Related Questions