Changes between Version 2 and Version 3 of Trigger Functions

Timestamp:

05/12/26 02:19:18 (2 weeks ago)

Author:

193284

Comment:

--

Trigger Functions

@@ -17 +17 @@
 {{collapse(View SQL Code)
 <syntaxhighlight lang="sql">
-PASTE SQL FROM advanced_triggers.sql
+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();
 </syntaxhighlight>
 }}
@@ -27 +51 @@
 This trigger prevents overlapping photographer bookings.
 
+=== SQL Code ===
+
+{{collapse(View SQL Code)
+<syntaxhighlight lang="sql">
+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();
+</syntaxhighlight>
+}}
+
 == 3. Band Booking Conflict Prevention ==
 
@@ -33 +89 @@
 This trigger prevents overlapping band reservations.
 
+=== SQL Code ===
+
+{{collapse(View SQL Code)
+<syntaxhighlight lang="sql">
+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();
+</syntaxhighlight>
+}}
+
 == 4. Attendance Consistency Validation ==
 
@@ -38 +126 @@
 
 This trigger validates attendance records against RSVP responses.
+
+=== SQL Code ===
+
+{{collapse(View SQL Code)
+<syntaxhighlight lang="sql">
+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();
+</syntaxhighlight>
+}}