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();