auto updation in data base - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

auto updation in data base

Author

Topic

purushotham216
Starting Member

14 Posts

Posted - 2009-02-02 : 02:03:10

hi to all,

Thanks in advance.

in my database table userinfo, regid,username,password,startdate,status fields are columns.

default status is active(while inserting data into table).

how to update status as inactive after one month from startdate automatically?.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-02 : 02:13:40

just write a t-sql procedure which does this and schedule it as a job from sql agent to be executed daily at a convienient schedule. the logic will be like:-


UPDATE youtable
SET status='inactive'
WHERE startdate<DATEADD(mm,-1,GETDATE())
AND status='active'

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-02 : 05:40:26

try like using computed column
create table ta (id int, datat datetime )
alter table ta add status as case when datat <= GETDATE()-31 then 'inactive' else 'active' end
insert into ta select 1, '12/6/2008' union all select 1,'1/2/2009' union all select 1,'2/2/2009'
select * from ta
drop table ta

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-02 : 08:42:19

why do you need to do that? wont this cause the computed column to be unnecessarily calculated each time you want retrieve it?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources