Auto-update calculated column values

I want to add an additional column (Open Time) in my table which calculates the difference between latest DOOR OPEN and DOOR CLOSE. And is triggered by the message DOOR CLOSE. And I want this column to calculate the difference in time every time new rows are added to the column without me having to run the query again:

Date	TriggerTime	EventMessageheader	Open_Time
07/10/2024	3:31:00	Task 1	NULL
07/10/2024	3:42:53	DOOR OPEN	NULL
07/10/2024	3:43:53	Task 2	NULL
07/10/2024	3:57:00	DOOR CLOSE	0:14:07
07/10/2024	3:58:00	Task 3	NULL
07/10/2024	4:10:00	Task 4	NULL
07/10/2024	4:13:00	Task 5	NULL
07/10/2024	4:20:22	DOOR OPEN	NULL
07/10/2024	4:21:00	Task 7	NULL
07/10/2024	4:24:14	Task 8	NULL
07/10/2024	4:27:00	Task 9	NULL
07/10/2024	4:28:00	Task 10	NULL
07/10/2024	4:43:00	DOOR CLOSE	0:22:38

SQL code:

DROP TABLE IF EXISTS #Events;

CREATE TABLE #Events
(
    Date date,
    TriggerTime time(0) NOT NULL,
    EventMessage varchar(50) NOT NULL
);

INSERT #Events (Date,TriggerTime, EventMessage)
VALUES ('07/10/2024', '3:31:00', 'Task 1'),
       ('07/10/2024', '3:42:53', 'DOOR OPEN'),
       ('07/10/2024', '3:43:53', 'Task 2'),
       ('07/10/2024', '3:57:00', 'DOOR CLOSE'),
       ('07/10/2024', '3:58:00', 'Task 3'),
       ('07/10/2024', '4:10:00', 'Task 4'),
       ('07/10/2024', '4:13:00', 'Task 5'),
       ('07/10/2024', '4:20:22', 'DOOR OPEN'),
       ('07/10/2024', '4:21:00', 'Task 7'),
       ('07/10/2024', '4:24:14', 'Task 8'),
       ('07/10/2024', '4:27:00', 'Task 9'),
       ('07/10/2024', '4:28:00', 'Task 10'),
       ('07/10/2024', '4:43:00', 'DOOR CLOSE');

I tried using the following query, it works for calculating the column but I have rerun it to get updated values in open time column

ALTER TABLE #Events ADD Open_Time TIME(0);

-- Update this column with query value
;WITH CTE AS
(
    SELECT 
        *,
        SUM(CASE WHEN EventMessage LIKE 'DOOR%'THEN 1 ELSE 0 END)OVER(ORDER BY TriggerTime) AS Mark
    FROM 
        #Events
)
UPDATE C1
SET C1.Open_Time = CAST((CAST(C1.TriggerTime AS datetime)-CAST(C2.TriggerTime AS datetime)) AS time(0))  
FROM CTE C1 
LEFT JOIN CTE C2 ON C1.EventMessage = 'DOOR CLOSE' 
                 AND C2.EventMessage = 'DOOR OPEN' 
                 AND C2.Mark + 1 = C1.Mark

Solution

As I mention in the comments, don't store this in the table, instead calculate it using a VIEW. Then you can query the said VIEW instead and it'll always have the correct value.

The VIEW would look something like:

CREATE VIEW dbo.ViewName AS
    SELECT Date,
           TriggerTime,
           EventMessage,
           LAST_VALUE(CASE EventMessage WHEN 'DOOR OPEN' THEN TriggerTime END) IGNORE NULLS OVER (ORDER BY TriggerTime) AS OpenTime
    FROM dbo.YourTable;

If you aren't on 2022+ then you can use MAX, but you'll have to "jump through the hoop" of getting your separate date and time values into a single datetime value, and then you can CONVERT it back to a time:

CREATE OR ALTER VIEW dbo.ViewName AS
    SELECT Date,
           TriggerTime,
           EventMessage,
           CONVERT(time(0),MAX(CASE EventMessage WHEN 'DOOR OPEN' THEN CONVERT(datetime2(0),CONVERT(varchar(10),Date,112) + ' ' + CONVERT(varchar(10),TriggerTime,114)) END) OVER (ORDER BY TriggerTime)) AS OpenTime
    FROM dbo.YourTable;
GO