Что такое хранимые процедуры (stored procedures) и триггеры (triggers) в MySQL и каковы их плюсы и минусы?

Хранимые процедуры и триггеры в MySQL

Оба объекта хранятся в базе данных и выполняются на стороне сервера MySQL, что уменьшает сетевой трафик и позволяет централизовать бизнес-логику. Однако они же создают скрытые зависимости и затрудняют отладку.

Хранимые процедуры (Stored Procedures)

Процедура — именованная программа на диалекте SQL/PSM, вызываемая явно командой CALL. Поддерживает входные (IN), выходные (OUT) и двунаправленные (INOUT) параметры.

DELIMITER $$

CREATE PROCEDURE transfer_funds(
    IN  p_from   INT,
    IN  p_to     INT,
    IN  p_amount DECIMAL(12, 2),
    OUT p_status VARCHAR(50)
)
BEGIN
    DECLARE v_balance DECIMAL(12, 2);

    START TRANSACTION;

    SELECT balance INTO v_balance
    FROM accounts
    WHERE id = p_from
    FOR UPDATE;

    IF v_balance < p_amount THEN
        ROLLBACK;
        SET p_status = 'INSUFFICIENT_FUNDS';
    ELSE
        UPDATE accounts SET balance = balance - p_amount WHERE id = p_from;
        UPDATE accounts SET balance = balance + p_amount WHERE id = p_to;
        COMMIT;
        SET p_status = 'OK';
    END IF;
END$$

DELIMITER ;

-- Вызов
CALL transfer_funds(1, 2, 500.00, @result);
SELECT @result;

Триггеры (Triggers)

Триггер автоматически срабатывает до (BEFORE) или после (AFTER) операции INSERT, UPDATE или DELETE. Внутри триггера доступны псевдозаписи NEW (новые значения) и OLD (старые значения).

DELIMITER $$

-- Аудит изменений цены
CREATE TRIGGER trg_product_price_audit
AFTER UPDATE ON products
FOR EACH ROW
BEGIN
    IF OLD.price <> NEW.price THEN
        INSERT INTO price_history (product_id, old_price, new_price, changed_at)
        VALUES (NEW.id, OLD.price, NEW.price, NOW());
    END IF;
END$$

-- Валидация перед вставкой
CREATE TRIGGER trg_check_stock
BEFORE INSERT ON order_items
FOR EACH ROW
BEGIN
    DECLARE v_stock INT;
    SELECT stock INTO v_stock FROM products WHERE id = NEW.product_id;
    IF v_stock < NEW.quantity THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Insufficient stock';
    END IF;
END$$

DELIMITER ;

Просмотр существующих объектов

-- Список процедур
SHOW PROCEDURE STATUS WHERE Db = 'shop';

-- Тело процедуры
SHOW CREATE PROCEDURE transfer_funds;

-- Список триггеров
SHOW TRIGGERS FROM shop;

-- Удаление
DROP PROCEDURE IF EXISTS transfer_funds;
DROP TRIGGER IF EXISTS trg_product_price_audit;

Плюсы

• Меньше сетевых round-trip: сложная логика выполняется за один вызов.
• Переиспользование: несколько приложений (бэкенд, скрипты, ETL) используют одну процедуру.
• Триггеры гарантируют целостность данных на уровне БД — независимо от того, какое приложение делает изменение.
• Контроль доступа: можно выдать EXECUTE на процедуру без прямого доступа к таблицам.

Минусы

• Сложность отладки: нет нормального debugger'а, логирование через временные таблицы или SELECT.
• Процедуры и триггеры не покрываются обычными unit-тестами приложения.
• Бизнес-логика размазана между приложением и БД — усложняет понимание системы.
• Сложно вести в системе контроля версий (миграции нужны и для процедур).
• Масштабирование: процедуры выполняются на сервере БД, увеличивая нагрузку на него.

Подводные камни

• Триггер, вызывающий исключение SIGNAL, откатывает всю транзакцию — побочный эффект может удивить разработчиков приложения.
• Рекурсивные триггеры по умолчанию запрещены (@@global.trigger_recursion_depth = 0) — цепочки UPDATE внутри триггера не сработают.
• Триггеры BEFORE UPDATE могут изменять NEW, триггеры AFTER — нет; путаница приводит к незаметным отказам логики.
• При LOAD DATA INFILE и INSERT ... SELECT триггеры FOR EACH ROW срабатывают на каждую строку — значительная нагрузка при batch-операциях.
• Процедуры не кешируют план запроса между вызовами в одной сессии при изменении схемы — кеш инвалидируется.
• Забытый DELIMITER $$ при создании многострочных процедур через консоль приводит к синтаксической ошибке.
• Права: DEFINER по умолчанию — создавший процедуру пользователь; если его удалить, процедура перестаёт работать.
• MySQL не поддерживает функции, возвращающие таблицы (table-valued functions) — в отличие от PostgreSQL.

• Не уточнять storage engine и говорить о stored procedures и triggers так, будто MyISAM и InnoDB ведут себя одинаково.
• Игнорировать implicit commit, autocommit, isolation level или binlog там, где они меняют ответ.
• Описывать PostgreSQL-поведение как MySQL-поведение.
• Забывать проверить план через EXPLAIN и фактическую версию MySQL.

• Кандидат объясняет stored procedures и triggers через реальный механизм MySQL, а не общими словами.
• Приводит корректный SQL или диагностический запрос для этой СУБД.
• Называет ограничения, версионные отличия или эксплуатационные последствия.
• Связывает ответ с проектированием приложения, производительностью, надежностью или безопасностью.