Опис

<syntaxhighlight lang="sql"> CREATE OR REPLACE FUNCTION trg_prevent_double_booking() RETURNS TRIGGER AS $$ BEGIN

IF NEW.booking_status IN ('PENDING','CONFIRMED') THEN

IF EXISTS (

SELECT 1 FROM bookings b WHERE b.room_id = NEW.room_id

AND b.booking_status IN ('PENDING','CONFIRMED') AND daterange(b.check_in_date, b.check_out_date, '[)')

&& daterange(NEW.check_in_date, NEW.check_out_date, '[)')

) THEN

RAISE EXCEPTION 'Room % is already booked', NEW.room_id;

END IF;

END IF;

RETURN NEW;

END; $$ LANGUAGE plpgsql;

CREATE OR REPLACE TRIGGER prevent_double_booking BEFORE INSERT ON bookings FOR EACH ROW EXECUTE FUNCTION trg_prevent_double_booking(); </syntaxhighlight>