Хранимые процедуры и триггеры: логика в базе данных

Коротко: Хранимые процедуры — программы, хранящиеся в базе данных и вызываемые; триггеры выполняются автоматически при вставке, изменении или удалении данных.

Подробнее: Обычно приложение отправляет базе данных отдельные команды SQL. Но логику можно разместить и внутри базы данных. У этого есть преимущества и недостатки, которые следует взвесить.

Подробности

Хранимая процедура и функция

Именованная программа из SQL и управления потоком (циклы, условия), вызываемая через CALL или SELECT. Пример в PostgreSQL:

CREATE FUNCTION create_order(customer int, amount numeric)
RETURNS int LANGUAGE plpgsql AS $$
DECLARE new_id int;
BEGIN
  INSERT INTO orders (customer_id, amount) VALUES (customer, amount)
  RETURNING id INTO new_id;
  UPDATE customers SET revenue = revenue + amount WHERE id = customer;
  RETURN new_id;
END $$;

Языки: PL/pgSQL (PostgreSQL), T-SQL (SQL Server), PL/SQL (Oracle), процедуры MySQL.

Триггер

Триггер выполняется автоматически до или после INSERT, UPDATE или DELETE:

CREATE TRIGGER change_time
BEFORE UPDATE ON article
FOR EACH ROW EXECUTE FUNCTION set_change_time();

Типичное применение: установка времени изменения, запись журналов аудита, поддержание актуальности производных значений, проверка правил, которые не покрывает простое ограничение.

Преимущества

• Меньше сетевого трафика: один вызов вместо многих.
• Логика в одном месте, независимо от того, какое приложение обращается к данным.
• База данных гарантирует правила, даже если приложение о них забыло.
• Выполнение в транзакции рядом с данными.

Недостатки и альтернативы

Бизнес-логику в базе данных сложнее тестировать, версионировать и отлаживать, она привязывает к одному поставщику и может создавать пики нагрузки на трудно масштабируемый сервер базы данных. Скрытые эффекты триггеров удивляют разработчиков. Многие команды размещают логику в приложении, а в базе данных используют только ограничения, индексы и простые триггеры (аудит, отметка времени). Изменения процедур, как и код, относятся к миграциям и системе контроля версий.

См. также: SQL, Транзакции и ACID, Миграция базы данных, Журнал аудита