CODEWITHSUNDEEP
c#sql.netsql-server
RLHakim
RLHakim

Reputation: 3

best practice for change SQL server record automatically, after certain amount of time?

im currently looking for best practice for my problem ,

i have this table A, for every new record inserted the default value for column Status is Waiting . every record need to have a "timer" , in my case its 5 min . If its not set from Waiting to Completed in 5 minutes , it needs to be Expired

example :

| No. | Status   |
| --- | -------  |
| 1   | Expired  |
| 2   | Completed|
| 3   | Waiting  |

What's the best way to accomplish this ? whether its SQL server approach or C# Net. coding approach.

what i have tried , is making a computed column in SQL Server named Status_auto , and then put the logic inside the computed column query to set value based on Status and Create_date. It works as intended, but im not sure is it a really good method , since the table is expected to have thousands record and i'm worry about the performance issue later in production because of this computed column

Upvotes: 0

Views: 62

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

Put the time that the value was inserted into the table.

Then use a view to calculate the status.

The view looks like:

create view v_t as 
    select t.*,
           (case when getdate() > dateadd(minute, 5, waiting_start) and
                      status <> 'Completed'
                 then 'Expired'
                 else status
            end) as actual_status
    from t;

No updates or scheduling is needed if this is handled using comparisons.

Upvotes: 2

Related Questions