Changes between Version 5 and Version 6 of Trigger Functions


Ignore:
Timestamp:
05/13/26 19:57:09 (13 days ago)
Author:
193284
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • Trigger Functions

    v5 v6  
    1515=== SQL Code ===
    1616
    17 <pre>
    18 
     17{{{
     18#!sql
    1919CREATE OR REPLACE FUNCTION check_venue_booking_overlap()
    2020RETURNS TRIGGER AS $$
    2121BEGIN
     22    IF NEW.end_time <= NEW.start_time THEN
     23        RAISE EXCEPTION 'Invalid venue booking interval: end_time must be after start_time.';
     24    END IF;
     25
    2226    IF EXISTS (
    2327        SELECT 1
    24         FROM venue_booking
    25         WHERE venue_id = NEW.venue_id
    26           AND "date" = NEW."date"
    27           AND booking_id <> COALESCE(NEW.booking_id, -1)
     28        FROM venue_booking vb
     29        WHERE vb.venue_id = NEW.venue_id
     30          AND vb.status <> 'cancelled'
     31          AND vb.booking_id <> COALESCE(NEW.booking_id, -1)
    2832          AND (
    29                 NEW.start_time < end_time
    30             AND NEW.end_time > start_time
     33                (NEW."date" + NEW.start_time) < (vb."date" + vb.end_time)
     34            AND (NEW."date" + NEW.end_time)   > (vb."date" + vb.start_time)
    3135          )
    3236    ) THEN
    33         RAISE EXCEPTION 'Venue is already booked for this time interval.';
     37        RAISE EXCEPTION 'Venue is already booked for this date and time interval.';
    3438    END IF;
    3539
    … …  
    3842$$ LANGUAGE plpgsql;
    3943
     44DROP TRIGGER IF EXISTS trg_venue_booking_overlap ON venue_booking;
     45
    4046CREATE TRIGGER trg_venue_booking_overlap
    4147BEFORE INSERT OR UPDATE ON venue_booking
    4248FOR EACH ROW
    4349EXECUTE FUNCTION check_venue_booking_overlap();
    44 
    45 </pre>
     50}}}
    4651
    4752== 2. Photographer Booking Conflict Prevention ==
    … …  
    5358=== SQL Code ===
    5459
    55 <pre>
    56 
     60{{{
     61#!sql
    5762CREATE OR REPLACE FUNCTION check_photographer_booking_overlap()
    5863RETURNS TRIGGER AS $$
    5964BEGIN
     65    IF NEW.end_time <= NEW.start_time THEN
     66        RAISE EXCEPTION 'Invalid photographer booking interval: end_time must be after start_time.';
     67    END IF;
     68
    6069    IF EXISTS (
    6170        SELECT 1
    62         FROM photographer_booking
    63         WHERE photographer_id = NEW.photographer_id
    64           AND "date" = NEW."date"
    65           AND booking_id <> COALESCE(NEW.booking_id, -1)
     71        FROM photographer_booking pb
     72        WHERE pb.photographer_id = NEW.photographer_id
     73          AND pb.status <> 'cancelled'
     74          AND pb.booking_id <> COALESCE(NEW.booking_id, -1)
    6675          AND (
    67                 NEW.start_time < end_time
    68             AND NEW.end_time > start_time
     76                (NEW."date" + NEW.start_time) < (pb."date" + pb.end_time)
     77            AND (NEW."date" + NEW.end_time)   > (pb."date" + pb.start_time)
    6978          )
    7079    ) THEN
    71         RAISE EXCEPTION 'Photographer is already booked for this time interval.';
     80        RAISE EXCEPTION 'Photographer is already booked for this date and time interval.';
    7281    END IF;
    7382
    … …  
    7685$$ LANGUAGE plpgsql;
    7786
     87DROP TRIGGER IF EXISTS trg_photographer_booking_overlap ON photographer_booking;
     88
    7889CREATE TRIGGER trg_photographer_booking_overlap
    7990BEFORE INSERT OR UPDATE ON photographer_booking
    8091FOR EACH ROW
    8192EXECUTE FUNCTION check_photographer_booking_overlap();
    82 
    83 </pre>
     93}}}
    8494
    8595== 3. Band Booking Conflict Prevention ==
    … …  
    91101=== SQL Code ===
    92102
    93 <pre>
    94 
     103{{{
     104#!sql
    95105CREATE OR REPLACE FUNCTION check_band_booking_overlap()
    96106RETURNS TRIGGER AS $$
    97107BEGIN
     108    IF NEW.end_time <= NEW.start_time THEN
     109        RAISE EXCEPTION 'Invalid band booking interval: end_time must be after start_time.';
     110    END IF;
     111
    98112    IF EXISTS (
    99113        SELECT 1
    100         FROM band_booking
    101         WHERE band_id = NEW.band_id
    102           AND "date" = NEW."date"
    103           AND booking_id <> COALESCE(NEW.booking_id, -1)
     114        FROM band_booking bb
     115        WHERE bb.band_id = NEW.band_id
     116          AND bb.status <> 'cancelled'
     117          AND bb.booking_id <> COALESCE(NEW.booking_id, -1)
    104118          AND (
    105                 NEW.start_time < end_time
    106             AND NEW.end_time > start_time
     119                (NEW."date" + NEW.start_time) < (bb."date" + bb.end_time)
     120            AND (NEW."date" + NEW.end_time)   > (bb."date" + bb.start_time)
    107121          )
    108122    ) THEN
    109         RAISE EXCEPTION 'Band is already booked for this time interval.';
     123        RAISE EXCEPTION 'Band is already booked for this date and time interval.';
    110124    END IF;
    111125
    … …  
    114128$$ LANGUAGE plpgsql;
    115129
     130DROP TRIGGER IF EXISTS trg_band_booking_overlap ON band_booking;
     131
    116132CREATE TRIGGER trg_band_booking_overlap
    117133BEFORE INSERT OR UPDATE ON band_booking
    118134FOR EACH ROW
    119135EXECUTE FUNCTION check_band_booking_overlap();
    120 
    121 </pre>
     136}}}
    122137
    123138== 4. Attendance Consistency Validation ==
    … …  
    129144=== SQL Code ===
    130145
    131 <pre>
    132 
     146{{{
     147#!sql
    133148CREATE OR REPLACE FUNCTION validate_attendance_consistency()
    134149RETURNS TRIGGER AS $$
    … …  
    151166$$ LANGUAGE plpgsql;
    152167
     168DROP TRIGGER IF EXISTS trg_attendance_consistency ON attendance;
     169
    153170CREATE TRIGGER trg_attendance_consistency
    154171BEFORE INSERT OR UPDATE ON attendance
    155172FOR EACH ROW
    156173EXECUTE FUNCTION validate_attendance_consistency();
    157 
    158 </pre>
     174}}}