Data expiration

Question

I developed an application where the user can create tasks (like a an agenda, ERP or CRM) So, the user creates a task and that task has an expiration date. The idea is to erase the entry when the task expires.

I've been thinking solutions, like having a timer and so, but:

There is any method or way to create data that expires?? I mean, does MySQL, MSSQL (or any DB Manager) support something like this natively?

I would be great to have something like this:

CREATE TABLE [MyTASK] (expires on mydate action = DELETE){
     mydate,
     mysomething,
     myagain
}

And then, the FakeSQL erases the data when the field "mydate" expires.

Answer 1

Data won't expire itself. You'll have to run something to find and remove it.

MS SQL Server supports the concept of scheduled jobs. So you can specify a stored proc that erases anything over (say) a week old, and configure that job to run every night.

Answer 2

mySQL seems to have Event Schedulers since 5.1.

Answer 3

If storage space is not an issue, it's worth a thought about putting in a bit field to mark a record as deleted so that none of your data is lost in case you want to add a part to your application that can look at all previous tasks. Like some administrative tools. Let's say employees are making tasks, and a task has expired and you don't want normal users to be able to modify or access expired tasks, so you "Delete" them when they expire. But one day the big boss comes along and says he wants a list of all tasks in the last year. This bit field could come in handy. It's just a thought and there may be better suggestions out there