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?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
As Martin Smith said in the comments, there is no Date/Time based trigger, therefore you have two options.
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.