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 |