Make a table condition type in Mysql

Question

Hello, I am setting up a hotel bank in Mysql and I would like to know how to add a condition for date and time of entry (checkin) and date and time of departure(checkout) specific for there to be no double booking in the period reserved by the guest. Thank you! Lizypanta

Answer 1

You could create a trigger (Trigger) to run before entering the record in the table, like the example below, assuming you use the table "table_reservations" with the columns "quarto_id", "checkin" and "checkout":

CREATE TRIGGER `tabela_reservas_BEFORE_INSERT` BEFORE INSERT ON `tabela_reservas` FOR EACH ROW
BEGIN

IF(SELECT count(*) as total FROM tabela_reservas as tr WHERE NEW.quarto_id = tr.quarto_id AND ((NEW.checkin >= tr.checkin AND NEW.checkin <= tr.checkout) OR (NEW.checkout >= tr.checkin AND NEW.checkout <= tr.checkout)))
THEN
    -- AQUI VC IMPLEMENTA O QUE DESEJA FAZER NOS CASOS DE DUPLICIDADE (LEMBRANDO QUE ATÉ AQUI O NOVO REGISTOR AINDA NÃO FOI INSERIDO NO BANCO)
END IF;

END