Database Programming

Prevention of double booking the same room

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 trigger prevent_double_booking
    before insert on bookings for each row execute function trg_prevent_double_booking();

Validate the dates of a booking

create or replace function trg_validate_booking_dates()
returns trigger as $$
    begin
        if new.check_in_date >= new.check_out_date then
            raise exception 'Check out date must be after check in date';
        end if;
        if new.check_in_date < now() then
            raise exception 'Check in date cannot be in the past';
        end if;
        return new;
    end;
    $$ language plpgsql;

create trigger validate_booking_dates
    before insert or update on bookings for each row
    execute function trg_validate_booking_dates();

A review can be made only for a complete booking

create or replace function trg_review_only_completed_booking()
returns trigger
as $$
    declare v_status varchar;
    begin
        select booking_status
        into v_status
            from bookings
        where booking_id = new.booking_id;

        if v_status <> 'COMPLETED' then
            raise exception 'Can only review completed bookings';
        end if;
        return new;
    end;
    $$ language plpgsql;

create trigger review_only_completed_booking
    before insert or update on reviews for each row
    execute function trg_review_only_completed_booking();