Збережені процедури та тригери: логіка в базі даних

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

Докладніше: Зазвичай застосунок надсилає базі даних окремі команди 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, Міграція бази даних, Журнал аудиту