| Version 5 (modified by , 2 weeks ago) ( diff ) |
|---|
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
<pre>
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();
</pre>
2. Photographer Booking Conflict Prevention
Description
This trigger prevents overlapping photographer bookings.
SQL Code
<pre>
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();
</pre>
3. Band Booking Conflict Prevention
Description
This trigger prevents overlapping band reservations.
SQL Code
<pre>
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();
</pre>
4. Attendance Consistency Validation
Description
This trigger validates attendance records against RSVP responses.
SQL Code
<pre>
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();
</pre>
