Changes between Initial Version and Version 1 of Trigger Functions

Timestamp:

05/12/26 01:35:16 (2 weeks ago)

Author:

193284

Comment:

--

Trigger Functions

@@ +1 @@
+= Trigger Functions =
+
+== Description ==
+
+This section documents the PostgreSQL PL/pgSQL trigger functions implemented for automatic validation and conflict prevention.
+
+The triggers are executed automatically during INSERT and UPDATE operations.
+
+== 1. Venue Booking Conflict Prevention ==
+
+=== Description ===
+
+This trigger prevents overlapping venue bookings for the same venue and time interval.
+
+=== SQL Code ===
+
+{{{
+CREATE OR REPLACE FUNCTION check_venue_booking_overlap()
+RETURNS TRIGGER AS $$
+BEGIN
+    IF EXISTS (
+        SELECT 1
+        FROM venue_booking
+        WHERE venue_id = NEW.venue_id
+          AND "date" = NEW."date"
+          AND booking_id <> COALESCE(NEW.booking_id, -1)
+          AND (
+                NEW.start_time < end_time
+            AND NEW.end_time > start_time
+          )
+    ) THEN
+        RAISE EXCEPTION 'Venue is already booked for this time interval.';
+    END IF;
+
+    RETURN NEW;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE TRIGGER trg_venue_booking_overlap
+BEFORE INSERT OR UPDATE ON venue_booking
+FOR EACH ROW
+EXECUTE FUNCTION check_venue_booking_overlap();
+}}}
+
+== 2. Photographer Booking Conflict Prevention ==
+
+=== Description ===
+
+This trigger prevents overlapping photographer bookings.
+
+=== SQL Code ===
+
+{{{
+CREATE OR REPLACE FUNCTION check_photographer_booking_overlap()
+RETURNS TRIGGER AS $$
+BEGIN
+    IF EXISTS (
+        SELECT 1
+        FROM photographer_booking
+        WHERE photographer_id = NEW.photographer_id
+          AND "date" = NEW."date"
+          AND booking_id <> COALESCE(NEW.booking_id, -1)
+          AND (
+                NEW.start_time < end_time
+            AND NEW.end_time > start_time
+          )
+    ) THEN
+        RAISE EXCEPTION 'Photographer is already booked for this time interval.';
+    END IF;
+
+    RETURN NEW;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE TRIGGER trg_photographer_booking_overlap
+BEFORE INSERT OR UPDATE ON photographer_booking
+FOR EACH ROW
+EXECUTE FUNCTION check_photographer_booking_overlap();
+}}}
+
+== 3. Band Booking Conflict Prevention ==
+
+=== Description ===
+
+This trigger prevents overlapping band reservations.
+
+=== SQL Code ===
+
+{{{
+CREATE OR REPLACE FUNCTION check_band_booking_overlap()
+RETURNS TRIGGER AS $$
+BEGIN
+    IF EXISTS (
+        SELECT 1
+        FROM band_booking
+        WHERE band_id = NEW.band_id
+          AND "date" = NEW."date"
+          AND booking_id <> COALESCE(NEW.booking_id, -1)
+          AND (
+                NEW.start_time < end_time
+            AND NEW.end_time > start_time
+          )
+    ) THEN
+        RAISE EXCEPTION 'Band is already booked for this time interval.';
+    END IF;
+
+    RETURN NEW;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE TRIGGER trg_band_booking_overlap
+BEFORE INSERT OR UPDATE ON band_booking
+FOR EACH ROW
+EXECUTE FUNCTION check_band_booking_overlap();
+}}}
+
+== 4. Attendance Consistency Validation ==
+
+=== Description ===
+
+This trigger validates attendance records against RSVP responses.
+
+=== SQL Code ===
+
+{{{
+CREATE OR REPLACE FUNCTION validate_attendance_consistency()
+RETURNS TRIGGER AS $$
+DECLARE
+    guest_rsvp VARCHAR(30);
+BEGIN
+    SELECT status
+    INTO guest_rsvp
+    FROM event_rsvp
+    WHERE guest_id = NEW.guest_id
+      AND event_id = NEW.event_id;
+
+    IF guest_rsvp = 'declined'
+       AND NEW.status = 'attending' THEN
+        RAISE EXCEPTION 'Declined guests cannot be marked as attending.';
+    END IF;
+
+    RETURN NEW;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE TRIGGER trg_attendance_consistency
+BEFORE INSERT OR UPDATE ON attendance
+FOR EACH ROW
+EXECUTE FUNCTION validate_attendance_consistency();
+}}}