First, let me thanks to my colleague Rajnish for this post.
When a user registers in the database, there is one field status and we manually set it Active(By Default).
When the user reaches his ExpiryDate, we have to set Status field from Active to Deactive by Admin Panel.
We can achieve this in an easy way by simply using Computed Column.
What is Computed Column ?
A computed column is computed from an expression that can use other columns in the same table.
I have no intention in covering this subject in a very deep level, but just to give my own personal experiment with Computed Column. If you want more details information, check link.
http://msdn.microsoft.com/en-us/library/ms191250%28v=sql.105%29.aspx
Here is the sample script to create a table with Status field(Computed Column)
[code]
create table tbl_User
(
userid int identity primary key,
Name varchar(30),
CreateOn Datetime Default GETDATE(),
ExpiryDate Datetime Default DateAdd(Minute,2,GETDATE()),
[Status] AS CASE WHEN ExpiryDate > GETDATE() THEN 1 ELSE 0 END
)
[/code]
Now insert some entries into the table tbl_User
[code]
insert into tbl_User(Name) values(‘elizabeth’)
insert into tbl_User(Name) values(‘Alastair’)
[/code]
Now select Data from table and you will see status field Active (1)
[code]
select * from tbl_User
[/code]
After 2 min (which is expiration time in my example) again select data from table you will find Status field value, Deactive(0).
I hope this is an easy solution.
Final Clean Up Act
— Clean up
[code]DROP TABLE tbl_User[/code]
