sql_labs/lab6_triggers.sql - master - nmktth/web-development-sem-4

sql_labs/lab6_triggers.sql
308 строк13 KB
artem
Небольшие исправления SQL-лабораторных
15 июн 2026, 01:49
15 июн 2026, 01:49c75995d
-- =========================================================
-- ЛАБОРАТОРНАЯ РАБОТА №6: ТРИГГЕРЫ И АУДИТ
-- =========================================================

-- Таблицы для логирования. Повторный запуск скрипта не должен удалять
-- накопленную историю аудита.

-- Таблица для истории прайсов и статусов
CREATE TABLE IF NOT EXISTS realty_ads_audit (
    audit_id BIGSERIAL PRIMARY KEY,
    advertisement_id BIGINT NOT NULL,
    old_price NUMERIC(10,2), new_price NUMERIC(10,2),
    old_status BOOLEAN, new_status BOOLEAN,
    changed_at TIMESTAMP NOT NULL DEFAULT NOW(),
    changed_by TEXT NOT NULL DEFAULT CURRENT_USER
);

-- Глобальный логер, пишет вообще все изменения в базу
CREATE TABLE IF NOT EXISTS realty_row_change_log (
    log_id BIGSERIAL PRIMARY KEY,
    table_name TEXT NOT NULL,
    attribute_name TEXT NOT NULL,
    old_value TEXT, new_value TEXT,
    changed_at TIMESTAMP NOT NULL DEFAULT NOW()
);


-- 1.1 Триггер для проверки лимита. Если юзер спамит объявами — не даем создать.
CREATE OR REPLACE FUNCTION trg_realty_check_ad_limit()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
DECLARE v_count INT;
BEGIN
    SELECT COUNT(*) INTO v_count FROM realty_advertisement WHERE user_id = NEW.user_id AND is_active = true;
    IF v_count >= 10 THEN
        RAISE EXCEPTION 'Эй, юзер % превысил лимит активных объявлений (макс. 10)', NEW.user_id;
    END IF;
    RETURN NEW;
END; $$;

DROP TRIGGER IF EXISTS trg_realty_check_ad_limit ON realty_advertisement;
CREATE TRIGGER trg_realty_check_ad_limit BEFORE INSERT ON realty_advertisement
FOR EACH ROW EXECUTE FUNCTION trg_realty_check_ad_limit();

-- 1.2 Если юзер удалился (или его забанили), чистим его заявки на соседей.
CREATE OR REPLACE FUNCTION trg_realty_cleanup_neighbor_requests()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
    UPDATE realty_neighborrequest SET is_active = false WHERE user_id = OLD.id AND is_active = true;
    RETURN OLD;
END; $$;

DROP TRIGGER IF EXISTS trg_realty_cleanup_neighbor_requests ON realty_user;
CREATE TRIGGER trg_realty_cleanup_neighbor_requests AFTER DELETE ON realty_user
FOR EACH ROW EXECUTE FUNCTION trg_realty_cleanup_neighbor_requests();

-- 1.3 Антиспам для чатов, помогает от флуда в личке.
CREATE OR REPLACE FUNCTION trg_realty_check_message_interval()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
    IF EXISTS (SELECT 1 FROM realty_message WHERE sender_id = NEW.sender_id AND sent_at > NOW() - INTERVAL '5 seconds') THEN
        RAISE EXCEPTION 'Слишком частая отправка сообщений. Остынь, бро (разрешено раз в 5 сек)';
    END IF;
    RETURN NEW;
END; $$;

DROP TRIGGER IF EXISTS trg_realty_check_message_interval ON realty_message;
CREATE TRIGGER trg_realty_check_message_interval BEFORE INSERT ON realty_message
FOR EACH ROW EXECUTE FUNCTION trg_realty_check_message_interval();

-- 1.4 Автоматически накручивает просмотры, если хату добавили в избранное.
CREATE OR REPLACE FUNCTION trg_realty_increment_views_on_fav()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
    UPDATE realty_advertisement SET views_count = views_count + 1 WHERE id = NEW.advertisement_id;
    RETURN NEW;
END; $$;

DROP TRIGGER IF EXISTS trg_realty_increment_views_on_fav ON realty_favorite;
CREATE TRIGGER trg_realty_increment_views_on_fav AFTER INSERT ON realty_favorite
FOR EACH ROW EXECUTE FUNCTION trg_realty_increment_views_on_fav();

-- 1.5 Не дает редактировать архивные посты, пока их не активируют.
CREATE OR REPLACE FUNCTION trg_realty_lock_archived_ads()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
    IF OLD.is_active = false AND NEW.is_active = false THEN
        RAISE EXCEPTION 'Нельзя трогать архивное объявление (ID: %).', OLD.id;
    END IF;
    RETURN NEW;
END; $$;

DROP TRIGGER IF EXISTS trg_realty_lock_archived_ads ON realty_advertisement;
CREATE TRIGGER trg_realty_lock_archived_ads BEFORE UPDATE ON realty_advertisement
FOR EACH ROW EXECUTE FUNCTION trg_realty_lock_archived_ads();

-- 1.6 Защита от дурака: если город слишком короткий в заявке на соседа.
CREATE OR REPLACE FUNCTION trg_realty_validate_neighbor_city()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
    IF LENGTH(NEW.city) < 2 THEN
        RAISE EXCEPTION 'Город не может состоять из 1 буквы';
    END IF;
    RETURN NEW;
END; $$;

DROP TRIGGER IF EXISTS trg_realty_validate_neighbor_city ON realty_neighborrequest;
CREATE TRIGGER trg_realty_validate_neighbor_city BEFORE INSERT OR UPDATE ON realty_neighborrequest
FOR EACH ROW EXECUTE FUNCTION trg_realty_validate_neighbor_city();

-- 1.7 Запрет жаловаться на самого себя (чтобы не абузили систему репортов).
CREATE OR REPLACE FUNCTION trg_realty_prevent_self_complaint()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
DECLARE v_target_user_id BIGINT;
BEGIN
    IF NEW.content_type = 'advertisement' THEN
        SELECT user_id INTO v_target_user_id FROM realty_advertisement WHERE id = NEW.content_id;
        IF v_target_user_id = NEW.complainant_id THEN
            RAISE EXCEPTION 'Жаловаться на самого себя? Серьезно?';
        END IF;
    END IF;
    RETURN NEW;
END; $$;

DROP TRIGGER IF EXISTS trg_realty_prevent_self_complaint ON realty_complaint;
CREATE TRIGGER trg_realty_prevent_self_complaint BEFORE INSERT ON realty_complaint
FOR EACH ROW EXECUTE FUNCTION trg_realty_prevent_self_complaint();

-- 1.8 Чистит текст сообщения от лишних пробелов по краям перед вставкой в БД.
CREATE OR REPLACE FUNCTION trg_realty_format_message_text()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
    NEW.text := TRIM(NEW.text);
    RETURN NEW;
END; $$;

DROP TRIGGER IF EXISTS trg_realty_format_message_text ON realty_message;
CREATE TRIGGER trg_realty_format_message_text BEFORE INSERT ON realty_message
FOR EACH ROW EXECUTE FUNCTION trg_realty_format_message_text();

-- 1.9 Если юзер борзеет и ставит конский залог (больше x3 от цены), мы его тормозим.
CREATE OR REPLACE FUNCTION trg_realty_validate_deposit()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
    IF NEW.deposit > NEW.price * 3 THEN
        RAISE EXCEPTION 'Сорян, залог (%) слишком огромный для такой аренды (%)', NEW.deposit, NEW.price;
    END IF;
    RETURN NEW;
END; $$;

DROP TRIGGER IF EXISTS trg_realty_validate_deposit ON realty_advertisement;
CREATE TRIGGER trg_realty_validate_deposit BEFORE INSERT OR UPDATE ON realty_advertisement
FOR EACH ROW EXECUTE FUNCTION trg_realty_validate_deposit();

-- 1.10 Если в заголовок вписали "продано", триггер сам вырубит объявление.
CREATE OR REPLACE FUNCTION trg_realty_mark_hot_ad()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
    IF NEW.title ILIKE '%продано%' THEN
        NEW.is_active := false;
    END IF;
    RETURN NEW;
END; $$;

DROP TRIGGER IF EXISTS trg_realty_mark_hot_ad ON realty_advertisement;
CREATE TRIGGER trg_realty_mark_hot_ad BEFORE UPDATE ON realty_advertisement
FOR EACH ROW EXECUTE FUNCTION trg_realty_mark_hot_ad();

-- 1.11 Логирует в консоль, если юзер поменял свой email.
CREATE OR REPLACE FUNCTION trg_realty_cascade_user_email()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
    IF OLD.email IS DISTINCT FROM NEW.email THEN
        RAISE NOTICE 'Юзер % сменил почту с % на %', NEW.id, OLD.email, NEW.email;
    END IF;
    RETURN NEW;
END; $$;

DROP TRIGGER IF EXISTS trg_realty_cascade_user_email ON realty_user;
CREATE TRIGGER trg_realty_cascade_user_email AFTER UPDATE OF email ON realty_user
FOR EACH ROW EXECUTE FUNCTION trg_realty_cascade_user_email();

-- 1.12 Аудит цен: скидывает в отдельную таблицу все прайс-дропы и подорожания.
CREATE OR REPLACE FUNCTION trg_realty_audit_price_changes()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
    IF OLD.price IS DISTINCT FROM NEW.price OR OLD.is_active IS DISTINCT FROM NEW.is_active THEN
        INSERT INTO realty_ads_audit(advertisement_id, old_price, new_price, old_status, new_status)
        VALUES (NEW.id, OLD.price, NEW.price, OLD.is_active, NEW.is_active);
    END IF;
    RETURN NEW;
END; $$;

DROP TRIGGER IF EXISTS trg_realty_audit_price_changes ON realty_advertisement;
CREATE TRIGGER trg_realty_audit_price_changes AFTER UPDATE ON realty_advertisement
FOR EACH ROW EXECUTE FUNCTION trg_realty_audit_price_changes();

-- 1.13 Дает лояльным юзерам бонус (скидка 10% на залог при повторном размещении).
CREATE OR REPLACE FUNCTION trg_realty_apply_loyalty_discount()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
DECLARE v_has_history BOOLEAN;
BEGIN
    SELECT EXISTS (SELECT 1 FROM realty_advertisement WHERE user_id = NEW.user_id AND city = NEW.city AND id <> COALESCE(NEW.id, -1)) INTO v_has_history;
    IF v_has_history THEN
        NEW.deposit := NEW.deposit * 0.9;
        RAISE NOTICE 'Применили скидос 10 процентов за лояльность в г. %', NEW.city;
    END IF;
    RETURN NEW;
END; $$;

DROP TRIGGER IF EXISTS trg_realty_apply_loyalty_discount ON realty_advertisement;
CREATE TRIGGER trg_realty_apply_loyalty_discount BEFORE INSERT ON realty_advertisement
FOR EACH ROW EXECUTE FUNCTION trg_realty_apply_loyalty_discount();


-- ЗАДАНИЕ 2: Каскадное удаление (ALTER TABLE)
-- Пересоздаем ключи, чтобы БД сама чистила зависимые записи при удалении
-- объявления или чата. Это аналог каскада для посадочных талонов в задании.
DO $$
DECLARE r RECORD;
BEGIN
    FOR r IN
        SELECT conrelid::regclass AS table_name, conname
        FROM pg_constraint
        WHERE contype = 'f'
          AND conrelid = 'realty_favorite'::regclass
          AND confrelid = 'realty_advertisement'::regclass
    LOOP
        EXECUTE format('ALTER TABLE %s DROP CONSTRAINT %I', r.table_name, r.conname);
    END LOOP;
END; $$;

ALTER TABLE realty_favorite
ADD CONSTRAINT realty_favorite_advertisement_id_fkey
FOREIGN KEY (advertisement_id) REFERENCES realty_advertisement(id) ON DELETE CASCADE;

DO $$
DECLARE r RECORD;
BEGIN
    FOR r IN
        SELECT conrelid::regclass AS table_name, conname
        FROM pg_constraint
        WHERE contype = 'f'
          AND conrelid = 'realty_chat'::regclass
          AND confrelid = 'realty_advertisement'::regclass
    LOOP
        EXECUTE format('ALTER TABLE %s DROP CONSTRAINT %I', r.table_name, r.conname);
    END LOOP;
END; $$;

ALTER TABLE realty_chat
ADD CONSTRAINT realty_chat_advertisement_id_fkey
FOREIGN KEY (advertisement_id) REFERENCES realty_advertisement(id) ON DELETE CASCADE;

DO $$
DECLARE r RECORD;
BEGIN
    FOR r IN
        SELECT conrelid::regclass AS table_name, conname
        FROM pg_constraint
        WHERE contype = 'f'
          AND conrelid = 'realty_message'::regclass
          AND confrelid = 'realty_chat'::regclass
    LOOP
        EXECUTE format('ALTER TABLE %s DROP CONSTRAINT %I', r.table_name, r.conname);
    END LOOP;
END; $$;

ALTER TABLE realty_message
ADD CONSTRAINT realty_message_chat_id_fkey
FOREIGN KEY (chat_id) REFERENCES realty_chat(id) ON DELETE CASCADE;


-- Примеры задач, которые лучше решать представлениями, а не триггерами:
-- агрегаты по пользователям, рейтинг объявлений и модерационный список жалоб.
-- Триггеры оставлены для проверок и автоматических действий при записи данных.
CREATE OR REPLACE VIEW v_realty_moderation_dashboard AS
SELECT
    a.id AS advertisement_id,
    a.title,
    a.city,
    a.price,
    COUNT(c.id) AS complaints_count,
    COUNT(f.id) AS favorites_count,
    a.views_count
FROM realty_advertisement a
LEFT JOIN realty_complaint c
    ON c.content_type = 'advertisement' AND c.content_id = a.id
LEFT JOIN realty_favorite f
    ON f.advertisement_id = a.id
GROUP BY a.id, a.title, a.city, a.price, a.views_count;


-- ЗАДАНИЕ 3: Глобальный логер изменений
CREATE OR REPLACE FUNCTION trg_realty_log_status_changes()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
    IF OLD.is_active IS DISTINCT FROM NEW.is_active THEN
        INSERT INTO realty_row_change_log(table_name, attribute_name, old_value, new_value) VALUES ('realty_advertisement', 'is_active', OLD.is_active::TEXT, NEW.is_active::TEXT);
    END IF;
    IF OLD.city IS DISTINCT FROM NEW.city THEN
        INSERT INTO realty_row_change_log(table_name, attribute_name, old_value, new_value) VALUES ('realty_advertisement', 'city', OLD.city, NEW.city);
    END IF;
    RETURN NEW;
END; $$;

DROP TRIGGER IF EXISTS trg_realty_log_status_changes ON realty_advertisement;
CREATE TRIGGER trg_realty_log_status_changes AFTER UPDATE ON realty_advertisement
FOR EACH ROW EXECUTE FUNCTION trg_realty_log_status_changes();