FunctionsProceduresTriggers: 01_triggers.sql

CREATE OR REPLACE FUNCTION trg_prevent_double_booking()
    RETURNS TRIGGER
    LANGUAGE plpgsql
AS $$
BEGIN
    IF NEW.status <> 'cancelled' THEN
        IF EXISTS (
            SELECT 1
            FROM appointment a
            WHERE a.slot_id = NEW.slot_id
              AND a.status <> 'cancelled'
              AND (
                TG_OP = 'INSERT'
                    OR a.appointment_id <> NEW.appointment_id
                )
        ) THEN
            RAISE EXCEPTION 'Slot % is already booked', NEW.slot_id;
        END IF;
    END IF;

    RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS prevent_double_booking ON appointment;

CREATE TRIGGER prevent_double_booking
    BEFORE INSERT OR UPDATE OF slot_id, status ON appointment
    FOR EACH ROW
EXECUTE FUNCTION trg_prevent_double_booking();