-2

I have table X and field EndTime(DateTime) and Status(int) Status has 2 condition. 1 for booked and 0 for available

I want to Auto update Status value from 1 to 0 when EndTime(DateTime) is equal to Current Time.

How I make it ?

Note : it's like for registration of people who are in the building, and from what time and to what time they are leaving. when time is over Status(int) will change from 1 to 0 automatically.

sqluser
  • 5,502
  • 7
  • 36
  • 50
azalikaEriya
  • 195
  • 1
  • 4
  • 17
  • Some databases, e.g. SQL Server, let you schedule jobs to run at a regular interval. You could run a stored procedure every five minutes to update status values when the `EndTime` is in the past. Note that it may be more complicated than it seems. If you use local rather than universal time then you may have odd problems twice a year when time moves forward or back one hour. – HABO Jan 12 '15 at 03:01

1 Answers1

2

You didnt mention which database you are asking about. Lets assume MySQL and Postgres.

Short answer is that none of these databases offer a native way to update a row based on some criteria. You could use a trigger to execute a function that will be run when a row is updated, but there is no guarantee it will run for the criteria EndTime == CurrentTime.

I think what you will need to do is implement an external process which runs on a scheduled basis, e.g. cron, and find all rows about to expire and update them yourself.

Cody Caughlan
  • 32,456
  • 5
  • 63
  • 68
  • would you mind to give me the example of code ? i'm pretty new in database. i use sql-server 2008 and c# .Net for build application. – azalikaEriya Jan 12 '15 at 02:48