SQL Server 2017

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

CREATE TABLE [dbo].[log] ( id INT, code INT, last_updated DATETIME )
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name=N'active_date' AND OBJECT_ID = OBJECT_ID(N'[dbo].[Log]'))
    ALTER TABLE [dbo].[Log] ADD active_date DATETIME NULL
SELECT * FROM log


id
code
last_updated
active_date


CREATE TRIGGER dbo.log__set_active_date
ON dbo.log
AFTER INSERT, UPDATE
AS 
BEGIN
  SET NOCOUNT ON;

  UPDATE
    log
  SET
    active_date = INSERTED.last_updated
  FROM
    dbo.log
  INNER JOIN
    INSERTED
      ON log.id = INSERTED.id
  WHERE
        INSERTED.code    = 10
    AND log.active_date IS NULL
END
INSERT INTO log (id, code, last_updated) VALUES (1,  0, GETDATE());
SELECT * FROM log;


id
code
last_updated
active_date


1
0
2021-08-20 21:20:36.463
null


WAITFOR DELAY '00:00:00.1'
INSERT INTO log (id, code, last_updated) VALUES (2, 10, GETDATE());
SELECT * FROM log;


id
code
last_updated
active_date


1
0
2021-08-20 21:20:36.463
null

2
10
2021-08-20 21:20:36.587
2021-08-20 21:20:36.587


UPDATE log SET code = 10 WHERE id = 1;
SELECT * FROM log;


id
code
last_updated
active_date


1
10
2021-08-20 21:20:36.463
2021-08-20 21:20:36.463

2
10
2021-08-20 21:20:36.587
2021-08-20 21:20:36.587


WAITFOR DELAY '00:00:00.1'
UPDATE log SET code = 0, last_updated = GETDATE();
SELECT * FROM log;


id
code
last_updated
active_date


1
0
2021-08-20 21:20:36.697
2021-08-20 21:20:36.463

2
0
2021-08-20 21:20:36.697
2021-08-20 21:20:36.587


UPDATE log SET code = 10;
SELECT * FROM log;


id
code
last_updated
active_date


1
10
2021-08-20 21:20:36.697
2021-08-20 21:20:36.463

2
10
2021-08-20 21:20:36.697
2021-08-20 21:20:36.587

id	code	last_updated	active_date
1	0	2021-08-20 21:20:36.463	null
2	10	2021-08-20 21:20:36.587	2021-08-20 21:20:36.587

id	code	last_updated	active_date
1	0	2021-08-20 21:20:36.697	2021-08-20 21:20:36.463
2	0	2021-08-20 21:20:36.697	2021-08-20 21:20:36.587