Is it possible to write a function that changes the value of a table automatically depending on the time?

Question

SQL

I am writing a website for hotel booking. I want to automatically deactivate reservations if the guest does not show up.
I store the status in the reservation table. If the term has expired, then change the status to deactive. Can this be done with sql or Asp itself? I hope I was able to explain what I want to do.

Answer 1

I would generally use one field for example:

create table bookings (
    id int,
    active_til datetame
);

And you don't need to edit anything.

Answer 2

Scheduler on the DBMS side, or on the app side. But it's easier and better to just fix the time until which the reservation is active and check it when polling the status.

Answer 3

Trigger.

Answer 4

Write down the time the room was reserved.
Then periodically (via cron, HostedService, Quartz, Hangfire, stored procedures with triggers, Task Scheduler or whatever) make a request like

UPDATE bookings WHERE status IS 'ACTIVE' AND add(reservation_date, '<тут твой срок>') > now() AND guest_visited IS FALSE SET status = 'INACTIVE';

.