Changes between Version 1 and Version 2 of Trigger Functions


Ignore:
Timestamp:
05/12/26 01:56:48 (2 weeks ago)
Author:
193284
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • Trigger Functions

    v1 v2  
    1515=== SQL Code ===
    1616
    17 {{{
    18 CREATE OR REPLACE FUNCTION check_venue_booking_overlap()
    19 RETURNS TRIGGER AS $$
    20 BEGIN
    21     IF EXISTS (
    22         SELECT 1
    23         FROM venue_booking
    24         WHERE venue_id = NEW.venue_id
    25           AND "date" = NEW."date"
    26           AND booking_id <> COALESCE(NEW.booking_id, -1)
    27           AND (
    28                 NEW.start_time < end_time
    29             AND NEW.end_time > start_time
    30           )
    31     ) THEN
    32         RAISE EXCEPTION 'Venue is already booked for this time interval.';
    33     END IF;
    34 
    35     RETURN NEW;
    36 END;
    37 $$ LANGUAGE plpgsql;
    38 
    39 CREATE TRIGGER trg_venue_booking_overlap
    40 BEFORE INSERT OR UPDATE ON venue_booking
    41 FOR EACH ROW
    42 EXECUTE FUNCTION check_venue_booking_overlap();
    43 }}}
     17{{collapse(View SQL Code)
     18<syntaxhighlight lang="sql">
     19PASTE SQL FROM advanced_triggers.sql
     20</syntaxhighlight>
     21}}
    4422
    4523== 2. Photographer Booking Conflict Prevention ==
    … …  
    4927This trigger prevents overlapping photographer bookings.
    5028
    51 === SQL Code ===
    52 
    53 {{{
    54 CREATE OR REPLACE FUNCTION check_photographer_booking_overlap()
    55 RETURNS TRIGGER AS $$
    56 BEGIN
    57     IF EXISTS (
    58         SELECT 1
    59         FROM photographer_booking
    60         WHERE photographer_id = NEW.photographer_id
    61           AND "date" = NEW."date"
    62           AND booking_id <> COALESCE(NEW.booking_id, -1)
    63           AND (
    64                 NEW.start_time < end_time
    65             AND NEW.end_time > start_time
    66           )
    67     ) THEN
    68         RAISE EXCEPTION 'Photographer is already booked for this time interval.';
    69     END IF;
    70 
    71     RETURN NEW;
    72 END;
    73 $$ LANGUAGE plpgsql;
    74 
    75 CREATE TRIGGER trg_photographer_booking_overlap
    76 BEFORE INSERT OR UPDATE ON photographer_booking
    77 FOR EACH ROW
    78 EXECUTE FUNCTION check_photographer_booking_overlap();
    79 }}}
    80 
    8129== 3. Band Booking Conflict Prevention ==
    8230
    … …  
    8533This trigger prevents overlapping band reservations.
    8634
    87 === SQL Code ===
    88 
    89 {{{
    90 CREATE OR REPLACE FUNCTION check_band_booking_overlap()
    91 RETURNS TRIGGER AS $$
    92 BEGIN
    93     IF EXISTS (
    94         SELECT 1
    95         FROM band_booking
    96         WHERE band_id = NEW.band_id
    97           AND "date" = NEW."date"
    98           AND booking_id <> COALESCE(NEW.booking_id, -1)
    99           AND (
    100                 NEW.start_time < end_time
    101             AND NEW.end_time > start_time
    102           )
    103     ) THEN
    104         RAISE EXCEPTION 'Band is already booked for this time interval.';
    105     END IF;
    106 
    107     RETURN NEW;
    108 END;
    109 $$ LANGUAGE plpgsql;
    110 
    111 CREATE TRIGGER trg_band_booking_overlap
    112 BEFORE INSERT OR UPDATE ON band_booking
    113 FOR EACH ROW
    114 EXECUTE FUNCTION check_band_booking_overlap();
    115 }}}
    116 
    11735== 4. Attendance Consistency Validation ==
    11836
    … …  
    12038
    12139This trigger validates attendance records against RSVP responses.
    122 
    123 === SQL Code ===
    124 
    125 {{{
    126 CREATE OR REPLACE FUNCTION validate_attendance_consistency()
    127 RETURNS TRIGGER AS $$
    128 DECLARE
    129     guest_rsvp VARCHAR(30);
    130 BEGIN
    131     SELECT status
    132     INTO guest_rsvp
    133     FROM event_rsvp
    134     WHERE guest_id = NEW.guest_id
    135       AND event_id = NEW.event_id;
    136 
    137     IF guest_rsvp = 'declined'
    138        AND NEW.status = 'attending' THEN
    139         RAISE EXCEPTION 'Declined guests cannot be marked as attending.';
    140     END IF;
    141 
    142     RETURN NEW;
    143 END;
    144 $$ LANGUAGE plpgsql;
    145 
    146 CREATE TRIGGER trg_attendance_consistency
    147 BEFORE INSERT OR UPDATE ON attendance
    148 FOR EACH ROW
    149 EXECUTE FUNCTION validate_attendance_consistency();
    150 }}}