AUTO UPDATE THE DEFAULT DATE in SQL Server

In a previous post, it was explained how to update the default date or date column in MySQL when a row gets updated. The clause “ON UPDATE CURRENT TIMESTAMP” is the way to define a default value for UPDATE.

However, there isn’t a clause like that in SQL Server (up till 2016).

To represent the date the row was updated, you must therefore create a trigger to update the “modified date” column. Here is an example –

CREATE TABLE MyTable
(
Id INT PRIMARY KEY,
StudentName VARCHAR(50) NOT NULL,
Address1 VARCHAR(50),
Address2 VARCHAR(50),
State VARCHAR(50),
dtJoined DATETIME DEFAULT CURRENT_TIMESTAMP,
dtLastUpdated DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER trgLastUpdated ON MyTable
AFTER UPDATE AS
BEGIN
UPDATE MyTable SET dtLastUpdated = GETDATE()
WHERE Id IN (SELECT Id FROM Inserted);
END;

You can use “PERIOD FOR SYSTEM TIME” in SQL Server 2016, which is the equivalent of MySQL’s “ON UPDATE CURRENT TIMESTAMP”. An example is given below-

CREATE TABLE MyTable
( Id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
StudentName VARCHAR(50) NOT NULL,
Address1 VARCHAR(50),
Address2 VARCHAR(50),
State VARCHAR(50),
dtJoined DATETIME2 DEFAULT CURRENT_TIMESTAMP,
LastUpdated DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
LastUpdated2 DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,
PERIOD FOR SYSTEM_TIME (LastUpdated,LastUpdated2) );

INSERT INTO MyTable (StudentName, Address1, Address2, State) VALUES
(‘Zafar Iqbal’,’DubaiLand’,’F412′,’Dubai’),
(‘Tahir Iqbal’,’DubaiLand’,’F413′,’Dubai’),
(‘Shafi Shaik’,’DubaiLand’,’F720′,’Dubai’);

Now, let’s test it with an UPDATE statement.

SELECT * FROM MyTable;
UPDATE MyTable SET Address2 = ‘F513’ WHERE Id=2;
SELECT * FROM MyTable;

Result: