= 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 ===

{{collapse(View SQL Code)
<syntaxhighlight lang="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>
}}

== 2. Photographer Booking Conflict Prevention ==

=== Description ===

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 ==

=== Description ===

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 ==

=== Description ===

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>
}}