How to create a trigger that update the date and time field and modify of the row

Question

I want to create trigger that will update the record. The situation is like:

I have one column named CheckInDateTime and one more named Status. If the administrator is not able to update the Status within 30 minutes of CheckInDateTime I want to update the Status column of that particular row.

How can I update that particular row?

Answer 1

Create a task in SQL Server Agent that runs a stored procedure once every minute that updates the affected rows.

For instance, something like this might work:

CREATE PROCEDURE UpdateRows
AS
BEGIN
    DECLARE @Cutoff DATETIME;
    SET @Cutoff = DATEADD(mi, -30, GETDATE());
    UPDATE MyTable
    SET Status = 'ForcefullyClosed'
    WHERE CheckInDateTime < @Cutoff AND Status IS NULL;
END;