Changes between Initial Version and Version 1 of DatabaseProgramming

Timestamp:

05/03/26 18:31:51 (23 hours ago)

Author:

231035

Comment:

--

DatabaseProgramming

@@ +1 @@
+= 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();
+}}}