Триггеры

Код, который база данных сама выполняет при `INSERT`, `UPDATE` и `DELETE`: автоматическое списание остатка, журнал аудита, отказ от неверных данных через `RAISE` и скрытые опасности триггеров.

  • Продвинутый
  • 22 мин
  • 12 вопросов
  • Урок 20 из 28

В этом уроке ты узнаешь

  • Создавать триггеры BEFORE, AFTER и INSTEAD OF и пользоваться в них строками NEW и OLD
  • Вести журнал аудита и поддерживать производные значения (например, остаток) с помощью триггеров
  • Останавливать неверные данные через RAISE(ABORT, ...) и различать режимы ABORT, FAIL, IGNORE и ROLLBACK
  • Видеть риски рекурсии и скрытой логики и сравнивать синтаксис SQLite, PostgreSQL и MySQL

В базе магазина каждый новый заказ должен уменьшать остаток товара, а каждое изменение цены — оставлять след: что и когда изменилось. Можно не забывать делать это в коде сайта — но ещё и в админке, в мобильном приложении и в ночном скрипте импорта. Забыл одно место — и остаток неверен. Триггер переносит такое правило в саму базу: СУБД выполняет его автоматически при каждом INSERT, UPDATE или DELETE, какая бы программа ни прислала команду. В этом уроке мы построим триггеры в SQLite (каждый пример можно запустить), увидим, где они помогают, а где вредят, и сравним синтаксис с PostgreSQL и MySQL.

Как устроен триггер

ТриггерОпределение

Именованный блок SQL-команд, хранящийся в базе и выполняемый автоматически, когда в заданной таблице или представлении происходит событие (INSERT, UPDATE или DELETE). Напрямую его не вызывают.

У триггера четыре части: момент (BEFORE — до изменения, AFTER — после, INSTEAD OF — вместо него), событие (INSERT, UPDATE, UPDATE OF столбец, DELETE), таблица и тело между BEGIN и END. Необязательное условие WHEN решает, выполнять ли тело для конкретной строки.

SQL
CREATE TRIGGER [IF NOT EXISTS] trigger_name
{BEFORE | AFTER | INSTEAD OF} {INSERT | UPDATE [OF col, ...] | DELETE}
ON table_name
[FOR EACH ROW]
[WHEN condition]
BEGIN
  statement;
  ...
END;
Общая форма в SQLite: квадратные скобки — необязательные части, фигурные — «выбери одно».

Внутри тела доступны две особые строки: **NEW — строка после изменения и OLD** — строка до него. Какие из них существуют, зависит от события:

СобытиеOLDNEW
INSERT—новая строка
UPDATEзначения до изменениязначения после изменения
DELETEудаляемая строка—

SQLite выполняет триггеры только для каждой строки (FOR EACH ROW): если команда меняет 5 строк, тело выполнится 5 раз (эти слова можно не писать). Наш первый триггер уменьшает остаток после каждого нового заказа. У Headphones было 40 штук, клиент 5 заказывает 4:

SQL
CREATE TRIGGER orders_reduce_stock
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
  UPDATE products
  SET stock = stock - NEW.quantity
  WHERE id = NEW.product_id;
END;

INSERT INTO orders (customer_id, product_id, quantity, order_date)
VALUES (5, 3, 4, '2025-08-01');

SELECT name, stock FROM products WHERE id = 3;
Ожидаемый результат
name | stock
Headphones | 36
Программа записала только заказ — остаток база уменьшила сама.

Чтобы читать дальше, зарегистрируйся бесплатно

Остальные разделы урока, тест и заметки открываются с аккаунтом Educora. Регистрация занимает минуту, а прогресс сохраняется на всех твоих устройствах.

Журнал аудита: AFTER UPDATE

Аудит — классическая работа для триггера. Создаём таблицу price_log и триггер, который срабатывает, только когда меняют столбец price (UPDATE OF price), и только если значение действительно другое (WHEN). Столбец changed_at получает текущее время автоматически; мы его не показываем, потому что при каждом запуске оно разное.

SQL
CREATE TABLE price_log (
  id         INTEGER PRIMARY KEY,
  product_id INTEGER,
  old_price  REAL,
  new_price  REAL,
  changed_at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER products_log_price
AFTER UPDATE OF price ON products
FOR EACH ROW
WHEN OLD.price <> NEW.price
BEGIN
  INSERT INTO price_log (product_id, old_price, new_price)
  VALUES (OLD.id, OLD.price, NEW.price);
END;

UPDATE products SET price = ROUND(price * 1.1, 2) WHERE category = 'Stationery';
UPDATE products SET price = 42 WHERE name = 'Chess set';   -- same price
UPDATE products SET stock = 7 WHERE name = 'Laptop';       -- price not touched

SELECT product_id, old_price, new_price FROM price_log ORDER BY id;
Ожидаемый результат
product_id | old_price | new_price
4 | 3.2 | 3.52
5 | 7.9 | 8.69

Выполнены три UPDATE, а в журнал попали две строки: цене Chess set снова присвоили 42 (её отсеял WHEN), а изменение остатка Laptop не трогало price, поэтому триггер вовсе не сработал.

Предскажи результат

Задача

Действует тот же триггер. Что окажется в price_log после UPDATE products SET price = NULL WHERE id = 1;? (Laptop стоил 1450.)

Показать решениеСкрыть решение

Решение

1) OLD.price <> NEW.price превращается в 1450 <> NULL, а результат — не «истина», а NULL.
2) WHEN считает NULL ложью, тело не выполняется.
3) Журнал остаётся пустым — цена исчезла бесследно! Нужно сравнение, устойчивое к NULL: OLD.price IS NOT NEW.price.
SQL
CREATE TABLE price_log (product_id INTEGER, old_price REAL, new_price REAL);

CREATE TRIGGER products_log_price
AFTER UPDATE OF price ON products
FOR EACH ROW
WHEN OLD.price IS NOT NEW.price
BEGIN
  INSERT INTO price_log VALUES (OLD.id, OLD.price, NEW.price);
END;

UPDATE products SET price = NULL WHERE id = 1;
UPDATE products SET price = 7.9  WHERE id = 5;   -- unchanged

SELECT * FROM price_log;
Ожидаемый результат
product_id | old_price | new_price
1 | 1450 | NULL
IS NOT ловит и переход к NULL, а неизменную цену по-прежнему пропускает.

BEFORE и INSTEAD OF: проверки и изменяемые представления

Ограничение CHECK (см. «CREATE TABLE: типы данных и ограничения») видит только саму строку. А правило «нельзя заказать больше, чем есть на складе» смотрит в другую таблицу — здесь помогает триггер BEFORE. Функция RAISE(ABORT, 'сообщение') останавливает команду с ошибкой и отменяет все строки, которые эта команда уже успела изменить. Остаток Monitor равен 0:

SQL
CREATE TRIGGER orders_check_stock
BEFORE INSERT ON orders
FOR EACH ROW
WHEN NEW.quantity > (SELECT stock FROM products WHERE id = NEW.product_id)
BEGIN
  SELECT RAISE(ABORT, 'not enough stock');
END;

INSERT INTO orders (customer_id, product_id, quantity, order_date)
VALUES (2, 9, 1, '2025-08-02');
Ожидаемый результат
Error: not enough stock

У RAISE четыре режима. Пусть внутри транзакции уже записан один заказ, а затем один INSERT добавляет три строки, и вторая из них нарушает правило:

РежимЧто происходитОстаётся новых строк
ABORTошибка; все строки этой команды отменяются, предыдущая команда остаётся1
FAILошибка; уже записанные этой командой строки остаются2
IGNOREошибки нет; пропускается только плохая строка3
ROLLBACKошибка; отменяется вся транзакция0
SQL
CREATE TRIGGER orders_skip_bad
BEFORE INSERT ON orders
FOR EACH ROW
WHEN NEW.quantity > (SELECT stock FROM products WHERE id = NEW.product_id)
BEGIN
  SELECT RAISE(IGNORE);
END;

INSERT INTO orders (customer_id, product_id, quantity, order_date)
VALUES (2, 4, 5, '2025-08-02'),
       (2, 9, 1, '2025-08-02'),    -- Monitor: skipped
       (4, 1, 2, '2025-08-02');

SELECT id, product_id, quantity FROM orders WHERE id > 12;
Ожидаемый результат
id | product_id | quantity
13 | 4 | 5
14 | 1 | 2
С IGNORE заказ Monitor молча отброшен, два других записаны.

В представление (мы знакомились с ними в уроке «Индексы, представления и транзакции»), соединяющее три таблицы, обычной командой строку не вставить: SQLite не знает, к какой таблице она относится. Триггер INSTEAD OF (разрешён только для представлений) объясняет это: «когда кто-то пишет в представление, сделай вместо этого вот что». Теперь программа может добавить заказ по именам клиента и товара, а id найдёт триггер.

SQL
CREATE VIEW order_lines AS
SELECT o.id, c.name AS customer, p.name AS product, o.quantity, o.order_date
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
JOIN products  AS p ON p.id = o.product_id;

CREATE TRIGGER order_lines_insert
INSTEAD OF INSERT ON order_lines
FOR EACH ROW
BEGIN
  INSERT INTO orders (customer_id, product_id, quantity, order_date)
  VALUES ((SELECT id FROM customers WHERE name = NEW.customer),
          (SELECT id FROM products  WHERE name = NEW.product),
          NEW.quantity, NEW.order_date);
END;

INSERT INTO order_lines (customer, product, quantity, order_date)
VALUES ('Olga Ivanova', 'Chess set', 2, '2025-08-03');

SELECT * FROM order_lines WHERE id > 11 ORDER BY id;
Ожидаемый результат
id | customer | product | quantity | order_date
12 | Anar Mustafayev | Notebook | 10 | 2025-07-07
13 | Olga Ivanova | Chess set | 2 | 2025-08-03

Цепочки триггеров: ловушки и мини-проект

Команды триггера могут запускать другие триггеры. Ниже вставка заказов запускает orders_reduce_stock, его UPDATE таблицы products — триггер products_log_stock, и один INSERT в итоге меняет три таблицы.

SQL
CREATE TABLE stock_log (product_id INTEGER, old_stock INTEGER, new_stock INTEGER);

CREATE TRIGGER orders_reduce_stock AFTER INSERT ON orders
BEGIN
  UPDATE products SET stock = stock - NEW.quantity WHERE id = NEW.product_id;
END;

CREATE TRIGGER products_log_stock AFTER UPDATE OF stock ON products
BEGIN
  INSERT INTO stock_log VALUES (NEW.id, OLD.stock, NEW.stock);
END;

INSERT INTO orders (customer_id, product_id, quantity, order_date)
VALUES (1, 4, 50, '2025-08-05'), (3, 8, 2, '2025-08-05');

SELECT * FROM stock_log;
Ожидаемый результат
product_id | old_stock | new_stock
4 | 500 | 450
8 | 120 | 118

Такие цепочки сильны, но невидимы: человек, читающий INSERT INTO orders в коде приложения, не подозревает, что меняются три таблицы. Триггер, изменяющий свою же таблицу, может даже вызвать сам себя. SQLite по умолчанию защищает: PRAGMA recursive_triggers выключена, и триггер сам себя повторно не запускает. Если её включить, обязательно нужно условие остановки, иначе:

SQL
PRAGMA recursive_triggers = ON;

CREATE TRIGGER products_bump AFTER UPDATE ON products
BEGIN
  UPDATE products SET stock = stock + 1 WHERE id = NEW.id;   -- updates itself
END;

UPDATE products SET price = 1400 WHERE id = 1;
Ожидаемый результат
Error: too many levels of trigger recursion

Мини-проект: учёт склада тремя триггерами. Зная ловушки, построим безопасную цепочку: каждый триггер делает одну работу, и его имя говорит, какую.

  1. Проверить

    BEFORE INSERT отклоняет количество больше остатка через RAISE(ABORT).

  2. Списать

    AFTER INSERT вычитает NEW.quantity из остатка.

  3. Вернуть

    AFTER DELETE возвращает на склад OLD.quantity отменённого заказа.

  4. Проверить на деле

    Добавь заказы, отмени один и посчитай остаток вручную: Laptop 8 − 3 + 3 = 8, Water bottle 120 − 20 = 100.

SQL
CREATE TRIGGER orders_check_stock BEFORE INSERT ON orders
WHEN NEW.quantity > (SELECT stock FROM products WHERE id = NEW.product_id)
BEGIN
  SELECT RAISE(ABORT, 'not enough stock');
END;
CREATE TRIGGER orders_take_stock AFTER INSERT ON orders
BEGIN
  UPDATE products SET stock = stock - NEW.quantity WHERE id = NEW.product_id;
END;
CREATE TRIGGER orders_return_stock AFTER DELETE ON orders
BEGIN
  UPDATE products SET stock = stock + OLD.quantity WHERE id = OLD.product_id;
END;

INSERT INTO orders (customer_id, product_id, quantity, order_date)
VALUES (4, 1, 3, '2025-08-10'), (4, 8, 20, '2025-08-10');
DELETE FROM orders WHERE id = 13;              -- the laptop order is cancelled

SELECT p.name, p.stock,
       (SELECT COUNT(*) FROM orders AS o WHERE o.product_id = p.id) AS orders
FROM products AS p
WHERE p.id IN (1, 8)
ORDER BY p.id;
Ожидаемый результат
name | stock | orders
Laptop | 8 | 1
Water bottle | 100 | 2

Триггеры в PostgreSQL и MySQL

В PostgreSQL тело не пишут внутри триггера: сначала создают триггерную функцию, которая RETURNS trigger (обычно на PL/pgSQL), затем триггер, вызывающий её через EXECUTE FUNCTION. Строчная функция BEFORE должна вернуть RETURN NEW (строку, которая будет сохранена, — её можно и изменить!) или NULL, чтобы пропустить строку; ошибка вызывается через RAISE EXCEPTION. Код проверен в PGlite (PostgreSQL 18 в WebAssembly):

SQL
CREATE FUNCTION orders_before_insert() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  IF NEW.quantity > (SELECT stock FROM products WHERE id = NEW.product_id) THEN
    RAISE EXCEPTION 'not enough stock for product %', NEW.product_id;
  END IF;
  UPDATE products SET stock = stock - NEW.quantity WHERE id = NEW.product_id;
  RETURN NEW;
END;
$$;

CREATE TRIGGER orders_stock
BEFORE INSERT ON orders
FOR EACH ROW EXECUTE FUNCTION orders_before_insert();

INSERT INTO orders (product_id, quantity) VALUES (3, 4);
SELECT name, stock FROM products ORDER BY id;
INSERT INTO orders (product_id, quantity) VALUES (9, 1);

Ожидаемый результат

    name    | stock
------------+-------
 Headphones |    36
 Monitor    |     0
(2 rows)

ERROR:  not enough stock for product 9
CONTEXT:  PL/pgSQL function orders_before_insert() line 4 at RAISE
PostgreSQL: проверка и списание остатка в одной функции.

MySQL пишет тело прямо в триггере, как SQLite, но клиенту нужен DELIMITER из-за точек с запятой внутри, а ошибка вызывается через SIGNAL. В триггере BEFORE можно написать SET NEW.столбец = ...; SQLite вообще не умеет менять NEW — там пишут триггер AFTER с UPDATE. Кроме того, триггер MySQL не может изменять свою же таблицу.

SQL
-- MySQL 8
DELIMITER //
CREATE TRIGGER orders_check_stock
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
  IF NEW.quantity > (SELECT stock FROM products WHERE id = NEW.product_id) THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'not enough stock';
  END IF;
  UPDATE products SET stock = stock - NEW.quantity WHERE id = NEW.product_id;
END//
DELIMITER ;
MySQL: то же правило; 45000 — код SQLSTATE для пользовательских ошибок.
ВозможностьSQLitePostgreSQLMySQL
ТелоBEGIN … ENDотдельная функция (PL/pgSQL)BEGIN … END
Уровеньтолько строкастрока и команда (FOR EACH STATEMENT)только строка
INSTEAD OFдля представленийдля представленийнет
Изменить NEW в BEFOREнельзяNEW.x := …; RETURN NEWSET NEW.x = …
Остановить с ошибкойRAISE(ABORT, '…')RAISE EXCEPTION '…'SIGNAL SQLSTATE '45000'
Несколько триггеров на одно событиепорядок не документированпо алфавиту имёнFOLLOWS / PRECEDES

Задание

Отменённые заказы не должны исчезать бесследно. Создай таблицу deleted_orders (order_id, customer_id, product_id, quantity) и триггер AFTER DELETE для orders, который копирует туда удалённую строку. Затем удали все заказы клиента 1 и покажи deleted_orders, отсортировав по order_id.

Задание · SQL
CREATE TABLE deleted_orders (
  order_id INTEGER, customer_id INTEGER, product_id INTEGER, quantity INTEGER
);

-- write the trigger here

DELETE FROM orders WHERE customer_id = 1;

SELECT * FROM deleted_orders ORDER BY order_id;
Ожидаемый результат
order_id | customer_id | product_id | quantity
1 | 1 | 1 | 1
2 | 1 | 3 | 2
12 | 1 | 4 | 10

Задание

Поддерживай счётчик в актуальном виде: в courses уже добавлен столбец enrolled INTEGER DEFAULT 0 и заполнен текущим числом записей. Создай триггер, который после каждой новой строки в enrollments увеличивает enrolled на 1. Запиши учеников 4 и 7 на курс 6 (Python Basics) и покажи title и enrolled курсов 6 и 7, отсортировав по id.

Задание · SQL
ALTER TABLE courses ADD COLUMN enrolled INTEGER DEFAULT 0;
UPDATE courses
SET enrolled = (SELECT COUNT(*) FROM enrollments AS e WHERE e.course_id = courses.id);

-- write the trigger here

INSERT INTO enrollments (student_id, course_id, enrolled_on)
VALUES (4, 6, '2025-10-01'), (7, 6, '2025-10-01');

SELECT title, enrolled FROM courses WHERE id IN (6, 7) ORDER BY id;
Ожидаемый результат
title | enrolled
Python Basics | 5
English B1 | 2

Запомни

  • Триггер выполняется автоматически при INSERT, UPDATE или DELETE — какая бы программа ни писала в базу.
  • NEW — строка после (INSERT, UPDATE), OLD — строка до (UPDATE, DELETE); WHEN и UPDATE OF сужают, когда срабатывает триггер.
  • BEFORE + RAISE(ABORT, ...) — для проверок, заглядывающих в другие таблицы; IGNORE молча пропускает строку.
  • Триггеры INSTEAD OF делают представления изменяемыми.
  • Триггеры образуют цепочки и невидимы: держи их немногочисленными, короткими и задокументированными; для NULL пиши IS NOT.
  • В PostgreSQL используется триггерная функция (RETURNS trigger, RETURN NEW), в MySQL — SIGNAL и SET NEW.x.
Заметил ошибку или неточность в уроке?

Проверь себя

Вопросов: 12. Каждый правильный ответ приносит XP.

Вопрос 1 / 12

Вопрос 1 / 12. Какая особая строка доступна в теле триггера AFTER DELETE?