I would like to get some expert opinon on using TTL (time-to-live) column in a table.
Here is the use case:
Each row would have a default TTL value, say 3600 seconds. This field can be modified / reset by a user / administrator. I would like to have a stored procedure to automatically go through rows in a table, check that TTL hasn't expired and if it hase change the sate of that row by modifying another field in that particular row. For example a table will have a column called TTL and another one called Active, once TTL has expired, value in Active field should be changed from TRUE to FALSE.
So my questions are:
Is this a common practice?
How is automation is achieved? (stored procedure or trigger)
Is this a good idea if the table has potentially over a million rows?
is there are a better way of achieving the same results in a more efficient way?
Any help will be greatly appreciated.
Thanks a lot