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 NEW.end_time <= NEW.start_time THEN
        RAISE EXCEPTION 'Invalid venue booking interval: end_time must be after start_time.';
    END IF;

    IF EXISTS (
        SELECT 1
        FROM venue_booking vb
        WHERE vb.venue_id = NEW.venue_id
          AND vb.status <> 'cancelled'
          AND vb.booking_id <> COALESCE(NEW.booking_id, -1)
          AND (
                ((NEW."date" + NEW.start_time) >= (vb."date" + vb.start_time)
                 AND (NEW."date" + NEW.start_time) < (vb."date" + vb.end_time))

                OR

                ((NEW."date" + NEW.end_time) > (vb."date" + vb.start_time)
                 AND (NEW."date" + NEW.end_time) <= (vb."date" + vb.end_time))

                OR

                ((NEW."date" + NEW.start_time) <= (vb."date" + vb.start_time)
                 AND (NEW."date" + NEW.end_time) >= (vb."date" + vb.end_time))

                OR

                ((NEW."date" + NEW.start_time) >= (vb."date" + vb.start_time)
                 AND (NEW."date" + NEW.end_time) <= (vb."date" + vb.end_time))
          )
    ) THEN
        RAISE EXCEPTION 'Venue is already booked for this date and time interval.';
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_venue_booking_overlap ON venue_booking;

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 NEW.end_time <= NEW.start_time THEN
        RAISE EXCEPTION 'Invalid photographer booking interval: end_time must be after start_time.';
    END IF;

    IF EXISTS (
        SELECT 1
        FROM photographer_booking pb
        WHERE pb.photographer_id = NEW.photographer_id
          AND pb.status <> 'cancelled'
          AND pb.booking_id <> COALESCE(NEW.booking_id, -1)
          AND (
                ((NEW."date" + NEW.start_time) >= (pb."date" + pb.start_time)
                 AND (NEW."date" + NEW.start_time) < (pb."date" + pb.end_time))

                OR

                ((NEW."date" + NEW.end_time) > (pb."date" + pb.start_time)
                 AND (NEW."date" + NEW.end_time) <= (pb."date" + pb.end_time))

                OR

                ((NEW."date" + NEW.start_time) <= (pb."date" + pb.start_time)
                 AND (NEW."date" + NEW.end_time) >= (pb."date" + pb.end_time))

                OR

                ((NEW."date" + NEW.start_time) >= (pb."date" + pb.start_time)
                 AND (NEW."date" + NEW.end_time) <= (pb."date" + pb.end_time))
          )
    ) THEN
        RAISE EXCEPTION 'Photographer is already booked for this date and time interval.';
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_photographer_booking_overlap ON photographer_booking;

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 NEW.end_time <= NEW.start_time THEN
        RAISE EXCEPTION 'Invalid band booking interval: end_time must be after start_time.';
    END IF;

    IF EXISTS (
        SELECT 1
        FROM band_booking bb
        WHERE bb.band_id = NEW.band_id
          AND bb.status <> 'cancelled'
          AND bb.booking_id <> COALESCE(NEW.booking_id, -1)
          AND (
                ((NEW."date" + NEW.start_time) >= (bb."date" + bb.start_time)
                 AND (NEW."date" + NEW.start_time) < (bb."date" + bb.end_time))

                OR

                ((NEW."date" + NEW.end_time) > (bb."date" + bb.start_time)
                 AND (NEW."date" + NEW.end_time) <= (bb."date" + bb.end_time))

                OR

                ((NEW."date" + NEW.start_time) <= (bb."date" + bb.start_time)
                 AND (NEW."date" + NEW.end_time) >= (bb."date" + bb.end_time))

                OR

                ((NEW."date" + NEW.start_time) >= (bb."date" + bb.start_time)
                 AND (NEW."date" + NEW.end_time) <= (bb."date" + bb.end_time))
          )
    ) THEN
        RAISE EXCEPTION 'Band is already booked for this date and time interval.';
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_band_booking_overlap ON band_booking;

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;

DROP TRIGGER IF EXISTS trg_attendance_consistency ON attendance;

CREATE TRIGGER trg_attendance_consistency
BEFORE INSERT OR UPDATE ON attendance
FOR EACH ROW
EXECUTE FUNCTION validate_attendance_consistency();