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

sql_labs/lab6_triggers.sql
222 строки10 KB
Artem Ilin
first_commit
14 июн 2026, 21:45
14 июн 2026, 21:4567055a5
-- =========================================================
-- ЛАБОРАТОРНАЯ РАБОТА №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; $$;

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; $$;

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; $$;

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; $$;

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; $$;

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; $$;

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; $$;

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; $$;

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; $$;

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; $$;

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; $$;

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; $$;

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; $$;

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)
-- Пересоздаем ключ, чтобы БД сама удаляла избранное, если хату снесли
ALTER TABLE realty_favorite DROP CONSTRAINT IF EXISTS realty_favorite_advertisement_id_fkey;
ALTER TABLE realty_favorite ADD CONSTRAINT realty_favorite_advertisement_id_fkey FOREIGN KEY (advertisement_id) REFERENCES realty_advertisement(id) ON DELETE CASCADE;


-- ЗАДАНИЕ 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; $$;

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