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?

  • 1
    Don't use a trigger for this. Just put whatever logic you were going to put into the trigger to determine this into the SELECT query instead. You can encapsulate the logic in a (non persisted) computed column on the table. – Martin Smith Oct 18 '12 at 7:12
  • And one important point I forgot to mention is that you can't create a trigger that fires at a specific time anyway. – Martin Smith Oct 18 '12 at 8:00

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.

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.