SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE dbo.Thing
(
    ThingID integer NOT NULL,
    ThingData integer NOT NULL,
    ModifiedBy AS ORIGINAL_LOGIN(),

    ValidFrom datetime2(7) 
        GENERATED ALWAYS AS ROW START 
        HIDDEN
        NOT NULL,
    ValidTo datetime2(7) 
        GENERATED ALWAYS AS ROW END 
        HIDDEN
        NOT NULL,

    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo),

    CONSTRAINT [PK dbo.Thing ThingID]
        PRIMARY KEY CLUSTERED (ThingID)
)
WITH 
(
    SYSTEM_VERSIONING = ON 
    (
        HISTORY_TABLE = dbo.ThingHistory
    )
);
CREATE TRIGGER t ON dbo.Thing 
    AFTER INSERT 
AS
-- Dummy update to generate an initial history row
-- with ValidFrom = ValidTo so it will be excluded
-- from temporal queries
UPDATE T
SET ThingData = T.ThingData
FROM Inserted AS I
JOIN dbo.Thing AS T
    ON T.ThingID = I.ThingID;
INSERT dbo.Thing (ThingID, ThingData) VALUES (1, 100);
INSERT dbo.Thing (ThingID, ThingData) VALUES (2, 200);

UPDATE dbo.Thing SET ThingData += 1;

DELETE dbo.Thing WHERE ThingID = 1;
7 rows affected

SELECT
    T.ThingID,
    T.ThingData
FROM dbo.Thing FOR SYSTEM_TIME ALL AS T;

SELECT
    T.ThingID,
    T.ThingData
FROM dbo.Thing AS T;

SELECT
    TH.ThingID,
    TH.ThingData,
    TH.ModifiedBy,
    Operation =
    CASE 
        WHEN 1 = ROW_NUMBER() OVER w
        THEN 'INSERT'
        WHEN LEAD(1) OVER w IS NOT NULL
        THEN 'UPDATE'
        WHEN EXISTS (SELECT * FROM dbo.Thing AS T WHERE T.ThingID = TH.ThingID)
        THEN 'UPDATE'
        ELSE 'DELETE'
    END,
    TH.ValidFrom,
    TH.ValidTo
FROM dbo.ThingHistory AS TH
WINDOW w AS (PARTITION BY TH.ThingID ORDER BY TH.ValidFrom)
ORDER BY
    TH.ThingID,
    TH.ValidFrom;


ThingID
ThingData


2
201

1
100




ThingID
ThingData


2
201




ThingID
ThingData
ModifiedBy
Operation
ValidFrom
ValidTo


1
100
fiddle_0714c780e1b042149988a07b2b8cb7bf
INSERT
2023-05-26 12:58:30.7551801
2023-05-26 12:58:30.7551801

1
100
fiddle_0714c780e1b042149988a07b2b8cb7bf
UPDATE
2023-05-26 12:58:30.7551801
2023-05-26 12:58:30.7862525

1
101
fiddle_0714c780e1b042149988a07b2b8cb7bf
DELETE
2023-05-26 12:58:30.7862525
2023-05-26 12:58:30.7862525

2
200
fiddle_0714c780e1b042149988a07b2b8cb7bf
INSERT
2023-05-26 12:58:30.7862525
2023-05-26 12:58:30.7862525

2
200
fiddle_0714c780e1b042149988a07b2b8cb7bf
UPDATE
2023-05-26 12:58:30.7862525
2023-05-26 12:58:30.7862525

ThingID	ThingData	ModifiedBy	Operation	ValidFrom	ValidTo
1	100	fiddle_0714c780e1b042149988a07b2b8cb7bf	INSERT	2023-05-26 12:58:30.7551801	2023-05-26 12:58:30.7551801
1	100	fiddle_0714c780e1b042149988a07b2b8cb7bf	UPDATE	2023-05-26 12:58:30.7551801	2023-05-26 12:58:30.7862525
1	101	fiddle_0714c780e1b042149988a07b2b8cb7bf	DELETE	2023-05-26 12:58:30.7862525	2023-05-26 12:58:30.7862525
2	200	fiddle_0714c780e1b042149988a07b2b8cb7bf	INSERT	2023-05-26 12:58:30.7862525	2023-05-26 12:58:30.7862525
2	200	fiddle_0714c780e1b042149988a07b2b8cb7bf	UPDATE	2023-05-26 12:58:30.7862525	2023-05-26 12:58:30.7862525