Update Database Fields Based On Date

Question

I need a trigger that updates a table row field by checking date that is stored in their own field without any interaction of user. i.e I am working on a project where I have to book rooms for user for a particular date and time and at that case I want to show room status as "booked" but after expiry of that date and time our database automatically update room status "booked" to "available". means I want that database keep track of their record and update it automatically. How I can achieve this?

Answer 1

In MySQL, I have used Event Scheduler. There is a similar function in SQLServer 2008, see Creating and Attaching Schedules to Jobs.

For your purpose, you can set it to start everyday at 00:01.

Answer 2

As Martin Smith said in the comments, there is no Date/Time based trigger, therefore you have two options.

1. Batch Update - Use a batch process to update the data. This requires determining how often the data needs to change and then creating a batch process that runs at least that often to update the data. If the information changes once a day this might be acceptable, but as the update need gets more frequent this solution becomes less usable.
2. Dynamic Evaluation - As Martin Smith suggested, determine availability when the information is requested. This can be in the select statement itself or you can use a computed column on the table. In either case the data will be as up to date as it can be.