Changes between Version 1 and Version 2 of DatabaseProgramming

Timestamp:

05/05/26 14:38:32 (3 weeks ago)

Author:

231035

Comment:

--

DatabaseProgramming

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