Make a table condition type in MySql

Question

Make a table condition type in MySql

Navigation

#1 by (0 votes)

0

Hello, I'm setting up a hotel bank in MySql and would like to know how to add a condition for checkin and specific checkout date so there is no duplication of reservations in the period booked by the hotel. guest. Thank you! Lizypanta

mysql sql java

asked by anonymous 30.03.2016 / 20:25

1 answer

Mongodb db.loadServerScripts (); fatal error missing} after property list nofile_b: 2 Skip beginTransaction commit PDO MYSQL

score 0 · Answer 1

You could create a trigger to execute before inserting the record into the table, as in the example below, assuming you use the table "table_reserves" 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