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();

Only an available room can be booked

create or replace function trg_room_is_available_for_booking()
returns trigger
as $$
    begin
    if new.check_in_date > new.check_out_date then
        raise exception 'Booking start date cannot be after end date';
    end if;


    if not exists(
        select 1
        from rooms r
        where r.room_id = new.room_id
        and r.status = 'ACTIVE'
    )then
        raise exception 'Room is not active';
    end if;

    if exists(
        select 1
        from bookings b
        where b.room_id = new.room_id
        and b.booking_status in ('PENDING','CONFIRMED')
        and b.booking_id <> new.booking_id
        and (
            (new.check_in_date,new.check_out_date)
            overlaps
            (b.check_in_date,b.check_out_date)
            )
    )then
        raise exception 'Room is already booked';
    end if;

    return new;

    end;
    $$ language plpgsql;
create or replace trigger book_only_available_room
    before insert or update on bookings for each row
    execute function trg_room_is_available_for_booking();