Update a Status Column Automatically

Question

I am creating a library management application with a membership form.

Types of Membership are:

1. Half yearly
2. Annually
3. Patron(For 15 Minutes)
4. Life Member

Now I have designed a table in SQL SERVER 2008 which consists of:

1. MemberId
2. Name
3. Joining Date
4. Expiration Date
5. Status

The status consists of either M for Member or E for Expired Membership.

Now I want this task to happen automatically.

For example, if the expiration date of membership was yesterday then it should automatically change the status to E.

How can I do this?

Answer 1

Use an expiration date of NULL to be your Life Member memberships. Then, create a SQL Server Agent job which kicks off the following statement and runs every 15 minutes (or 5):

UPDATE 
    Membership 
SET 
    Status = 'E' 
WHERE 
    Status = 'M' AND ExpirationDate < getdate() 
    AND ExpirationDate IS NOT NULL

Answer 2

You could create a SQL Job and schedule it to run every night to update the status flag. Here's an overview to get you started on it.

Comment: I'm not sure if you're treating your database as an independent datasource with this requirement. It looks to be more of a business case to update this status flag. What happens if you update the Expiration date later on? You'll also have to update that status flag. You might want your application to interpret the Expiration Date instead.

Thanks, -tom

Answer 3

How about using a computed column?

Inside the table designer select your status column, go to the column properties and select the node "computed column specification". Enter the formula there:

CASE WHEN ExpirationDate IS NOT NULL AND ExpirationDate < GETDATE() THEN 'E' ELSE 'M' END

The only negative thing about this is, that it cannot be indexed.

Answer 4

If you are loading your record into an object, why not use the properties of the object to show you "member"/"expired"?

This kind of automatic operation isn't really suited to a persistent data store. ou can use the computed columns as Greco suggested if you really need it, ut it is rightly pointed out that this can develop into a performance issue.