49 lines
1.4 KiB
PL/PgSQL
49 lines
1.4 KiB
PL/PgSQL
-- Balon uçuşu trip-level kısıtlaması
|
||
-- Bir trip içinde sadece 1 balon uçuşu olabilir
|
||
|
||
-- Trigger function to enforce one balloon per trip
|
||
CREATE OR REPLACE FUNCTION check_one_balloon_per_trip()
|
||
RETURNS TRIGGER AS $$
|
||
DECLARE
|
||
v_trip_id UUID;
|
||
v_place_type TEXT;
|
||
v_existing_count INTEGER;
|
||
BEGIN
|
||
-- Get the trip_id from trip_day_id
|
||
SELECT trip_id INTO v_trip_id
|
||
FROM trip_days
|
||
WHERE id = NEW.trip_day_id;
|
||
|
||
-- Get the place type
|
||
SELECT type INTO v_place_type
|
||
FROM places
|
||
WHERE id = NEW.place_id;
|
||
|
||
-- If this is a balloon place, check if trip already has one
|
||
IF v_place_type = 'hot-air-balloon' THEN
|
||
SELECT COUNT(*) INTO v_existing_count
|
||
FROM trip_places tp
|
||
JOIN trip_days td ON tp.trip_day_id = td.id
|
||
JOIN places p ON tp.place_id = p.id
|
||
WHERE td.trip_id = v_trip_id
|
||
AND p.type = 'hot-air-balloon'
|
||
AND tp.id != COALESCE(NEW.id, '00000000-0000-0000-0000-000000000000'::UUID);
|
||
|
||
IF v_existing_count > 0 THEN
|
||
RAISE EXCEPTION 'Bir seyahatte yalnızca 1 balon uçuşu eklenebilir';
|
||
END IF;
|
||
END IF;
|
||
|
||
RETURN NEW;
|
||
END;
|
||
$$ LANGUAGE plpgsql;
|
||
|
||
-- Create trigger for INSERT and UPDATE
|
||
DROP TRIGGER IF EXISTS enforce_one_balloon_per_trip ON trip_places;
|
||
CREATE TRIGGER enforce_one_balloon_per_trip
|
||
BEFORE INSERT OR UPDATE ON trip_places
|
||
FOR EACH ROW
|
||
EXECUTE FUNCTION check_one_balloon_per_trip();
|
||
|
||
COMMENT ON FUNCTION check_one_balloon_per_trip IS 'Ensures only one hot-air-balloon activity per trip';
|