Changes between Version 1 and Version 2 of DatabaseProgramming

Timestamp:

06/14/26 15:40:58 (3 days ago)

Author:

231072

Comment:

--

DatabaseProgramming

@@ -1 +1 @@
 == Опис
 
+<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>