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 everytime new rows are added to the column without me having to run the query again

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');
--Add a new column to Event table

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