В базе магазина каждый новый заказ должен уменьшать остаток товара, а каждое изменение цены — оставлять след: что и когда изменилось. Можно не забывать делать это в коде сайта — но ещё и в админке, в мобильном приложении и в ночном скрипте импорта. Забыл одно место — и остаток неверен. Триггер переносит такое правило в саму базу: СУБД выполняет его автоматически при каждом INSERT, UPDATE или DELETE, какая бы программа ни прислала команду. В этом уроке мы построим триггеры в SQLite (каждый пример можно запустить), увидим, где они помогают, а где вредят, и сравним синтаксис с PostgreSQL и MySQL.
Как устроен триггер
ТриггерОпределение
Именованный блок SQL-команд, хранящийся в базе и выполняемый автоматически, когда в заданной таблице или представлении происходит событие (INSERT, UPDATE или DELETE). Напрямую его не вызывают.
У триггера четыре части: момент (BEFORE — до изменения, AFTER — после, INSTEAD OF — вместо него), событие (INSERT, UPDATE, UPDATE OF столбец, DELETE), таблица и тело между BEGIN и END. Необязательное условие WHEN решает, выполнять ли тело для конкретной строки.
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;Внутри тела доступны две особые строки: **NEW — строка после изменения и OLD** — строка до него. Какие из них существуют, зависит от события:
| Событие | OLD | NEW |
|---|---|---|
| INSERT | — | новая строка |
| UPDATE | значения до изменения | значения после изменения |
| DELETE | удаляемая строка | — |
SQLite выполняет триггеры только для каждой строки (FOR EACH ROW): если команда меняет 5 строк, тело выполнится 5 раз (эти слова можно не писать). Наш первый триггер уменьшает остаток после каждого нового заказа. У Headphones было 40 штук, клиент 5 заказывает 4:
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 получает текущее время автоматически; мы его не показываем, потому что при каждом запуске оно разное.
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.)
Показать решениеСкрыть решение
Решение
OLD.price <> NEW.price превращается в 1450 <> NULL, а результат — не «истина», а NULL.2)
WHEN считает NULL ложью, тело не выполняется.3) Журнал остаётся пустым — цена исчезла бесследно! Нужно сравнение, устойчивое к NULL:
OLD.price IS NOT NEW.price.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:
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 |
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 найдёт триггер.
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 в итоге меняет три таблицы.
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 выключена, и триггер сам себя повторно не запускает. Если её включить, обязательно нужно условие остановки, иначе:
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
Мини-проект: учёт склада тремя триггерами. Зная ловушки, построим безопасную цепочку: каждый триггер делает одну работу, и его имя говорит, какую.
Проверить
BEFORE INSERTотклоняет количество больше остатка черезRAISE(ABORT).Списать
AFTER INSERTвычитаетNEW.quantityиз остатка.Вернуть
AFTER DELETEвозвращает на складOLD.quantityотменённого заказа.Проверить на деле
Добавь заказы, отмени один и посчитай остаток вручную: Laptop 8 − 3 + 3 = 8, Water bottle 120 − 20 = 100.
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):
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
MySQL пишет тело прямо в триггере, как SQLite, но клиенту нужен DELIMITER из-за точек с запятой внутри, а ошибка вызывается через SIGNAL. В триггере BEFORE можно написать SET NEW.столбец = ...; SQLite вообще не умеет менять NEW — там пишут триггер AFTER с UPDATE. Кроме того, триггер MySQL не может изменять свою же таблицу.
-- 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 ;45000 — код SQLSTATE для пользовательских ошибок.| Возможность | SQLite | PostgreSQL | MySQL |
|---|---|---|---|
| Тело | BEGIN … END | отдельная функция (PL/pgSQL) | BEGIN … END |
| Уровень | только строка | строка и команда (FOR EACH STATEMENT) | только строка |
INSTEAD OF | для представлений | для представлений | нет |
Изменить NEW в BEFORE | нельзя | NEW.x := …; RETURN NEW | SET 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.
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.
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.