For a long time, we have been using defaults for columns to reflect "created," but we've had to resort to triggers to handle updates to reflect "last modified." Wouldn't it be nice if SQL Server had something like MySQL's ON UPDATE?

CREATE TABLE `posts`
(
  PostID       INTEGER,
  LastModified TIMESTAMP NOT NULL ON UPDATE CURRENT_TIMESTAMP
)

Instead, we have to code and maintain after (or instead of) update triggers, and deal with multiple patterns that can either cause bugs after initial testing (like when someone assumes the trigger will only ever handle a single row) or force a logic change (like when someone uses @@ROWCOUNT but then starts using MERGE, against all good reason).

A hackaround

Since SQL Server 2016, we've had the ability to use Temporal Tables to keep historical versions of rows. Well, you can use some Temporal Table functionality without actually turning the feature on, as I show in this quick example:

CREATE TABLE dbo.Posts
(
  PostID       int NOT NULL PRIMARY KEY,
  Title        nvarchar(255) NOT NULL,  
  Created      datetime2 NOT NULL DEFAULT sysutcdatetime(),
  LastModified datetime2 GENERATED ALWAYS AS ROW START NOT NULL,
  Garbawgy     datetime2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,
  PERIOD FOR SYSTEM_TIME (LastModified, Garbawgy)
);
GO
 
INSERT dbo.Posts(PostID) VALUES(1),(2);
 
WAITFOR DELAY '00:00:05';
 
UPDATE dbo.Posts SET Title += N'a' WHERE PostID = 2;
 
SELECT * FROM dbo.Posts;

Results:

PostID  Title       Created                      LastModified
------  ----------  ---------------------------  ---------------------------
1       Post 1      2021-04-01 16:47:57.5717665  2021-04-01 16:47:57.5707663
2       Post 2a     2021-04-01 16:47:57.5717665  2021-04-01 16:48:02.5733807

Note that Garbawgy doesn't show up unless you explicitly ask for it.

There are some limitations that you need to be aware of:

• SYSTEM_TIME is always in UTC (which I prefer anyway, and which is why I also use that for Created).
• The time recorded is the start of the transaction, so sometimes the time won't accurately reflect the last change.
• The extra hidden column is not free.
• You can't update either column manually (still waiting for APPLICATION_TIME).
• You can't just drop the Garbawgy column without making other changes first.

If you got this far without laughing…

This is totally a prank on Michael J. Swart, who was embarrassed about posting on a topic that several other people had already written about:

• Erik Darling : Tracking Row Changes With Temporal Columns
• Daniel Hutmacher : How to add “created” and “updated” timestamps without triggers
• Martin Smith : Need a datetime column that automatically updates …

I don't think he should be embarrassed, in fact I think this is great: because everyone describes the scenario in a slightly different way, and that way could be the thing that makes the lightbulb click for a reader.

And I am far from innocent of things like this… I sometimes blog about things I've already blogged about. I'm also often surprised when I search for some topic and I land on a blog post I completely forgot I ever wrote.

Anyway, happy belated and back-dated April Fools'!