Триггеры в SQLite: CREATE TRIGGER, BEFORE/AFTER, OLD/NEW

Триггер запускает SQL автоматически

Триггер — это сохранённый блок SQL, который срабатывает каждый раз, когда на конкретной таблице происходит определённое событие. Пишете его один раз, а уже сама SQLite берёт на себя «когда именно» его выполнить.

Общий вид:

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price REAL NOT NULL
);

CREATE TABLE price_history (
    product_id INTEGER,
    old_price REAL,
    new_price REAL,
    changed_at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER log_price_change
AFTER UPDATE OF price ON products
BEGIN
    INSERT INTO price_history(product_id, old_price, new_price)
    VALUES (OLD.id, OLD.price, NEW.price);
END;

INSERT INTO products(name, price) VALUES ('Блокнот', 5.00);
UPDATE products SET price = 6.50 WHERE name = 'Блокнот';
SELECT * FROM price_history;

Мы ни разу не писали INSERT в price_history руками — всё сделал триггер. И теперь любое будущее изменение цены будет логироваться точно так же, неважно откуда оно пришло: из консоли, скрипта или приложения.

Разбираем CREATE TRIGGER по косточкам

Давайте пройдёмся по синтаксису шаг за шагом:

CREATE TRIGGER trigger_name
{ BEFORE | AFTER | INSTEAD OF } { INSERT | UPDATE [ OF column_list ] | DELETE }
ON table_name
[ FOR EACH ROW ]
[ WHEN condition ]
BEGIN
    -- одна или несколько инструкций
END;

• Момент срабатывания — BEFORE выполняется до изменения, AFTER — после, а INSTEAD OF подменяет операцию (только для представлений).
• Событие — какая операция запускает триггер. UPDATE OF col1, col2 сужает срабатывание до конкретных столбцов.
• Таблица — та, за которой ведётся наблюдение.
• FOR EACH ROW — SQLite поддерживает только построчные триггеры, так что эта конструкция подразумевается по умолчанию. Её можно писать явно для ясности — на поведение это никак не влияет.
• WHEN — необязательное условие. Тело триггера выполнится, только если оно истинно.
• Тело — один или несколько операторов между BEGIN и END. Каждый обязательно заканчивается точкой с запятой.

Вот, собственно, и весь синтаксис. Большинство реальных триггеров умещаются в пять–десять строк.

OLD и NEW: строка, которая меняется

Внутри тела триггера доступны две псевдострокы, через которые можно посмотреть данные:

• NEW — новая (входящая) строка. Доступна в триггерах на INSERT и UPDATE.
• OLD — текущая (старая) строка. Доступна в триггерах на UPDATE и DELETE.

В триггере на DELETE есть только OLD. В триггере на INSERT — только NEW. А в триггере на UPDATE доступны обе.

CREATE TABLE accounts (
    id INTEGER PRIMARY KEY,
    name TEXT,
    balance REAL NOT NULL
);

CREATE TABLE deletions (
    table_name TEXT,
    row_id INTEGER,
    snapshot TEXT,
    deleted_at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER archive_account
AFTER DELETE ON accounts
BEGIN
    INSERT INTO deletions(table_name, row_id, snapshot)
    VALUES ('accounts', OLD.id, OLD.name || ' / ' || OLD.balance);
END;

INSERT INTO accounts(name, balance) VALUES ('Rosa', 120.00);
DELETE FROM accounts WHERE name = 'Rosa';
SELECT * FROM deletions;

Удалённая строка исчезает из accounts, но перед этим её данные успевают попасть в deletions.

BEFORE: проверка или подгонка строки

Триггеры BEFORE срабатывают до того, как изменение реально запишется на диск. Это удобно, когда нужно бросить ошибку или нормализовать данные:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL,
    created_at TEXT
);

CREATE TRIGGER lowercase_email
BEFORE INSERT ON users
BEGIN
    UPDATE users SET email = email; -- пустая операция, просто для иллюстрации контекста
END;

CREATE TRIGGER reject_empty_email
BEFORE INSERT ON users
WHEN NEW.email IS NULL OR trim(NEW.email) = ''
BEGIN
    SELECT RAISE(ABORT, 'email не может быть пустым');
END;

INSERT INTO users(email) VALUES ('ada@example.com');
INSERT INTO users(email) VALUES ('');
SELECT * FROM users;

Второй INSERT прерывается ещё до того, как в таблицу попадёт хоть одна строка. RAISE(ABORT, '...') отменяет текущую инструкцию и откатывает её до начала, а RAISE(FAIL, ...), RAISE(ROLLBACK, ...) и RAISE(IGNORE) позволяют тоньше управлять тем, что произойдёт дальше.

Если речь о простой валидации данных, лучше использовать ограничения CHECK — они декларативны, и оптимизатор о них знает. Триггер BEFORE пригодится тогда, когда правило должно заглядывать в другие таблицы или делать то, что CHECK выразить не способен.

WHEN в триггере sqlite: условные триггеры

Условие WHEN отбирает, какие именно изменения строк запустят тело триггера. Оно вычисляется для каждой строки уже после того, как OLD и NEW получили свои значения:

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    total REAL NOT NULL,
    status TEXT NOT NULL
);

CREATE TABLE big_orders (
    order_id INTEGER,
    total REAL,
    flagged_at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER flag_big_order
AFTER INSERT ON orders
WHEN NEW.total >= 1000
BEGIN
    INSERT INTO big_orders(order_id, total) VALUES (NEW.id, NEW.total);
END;

INSERT INTO orders(total, status) VALUES (50, 'paid');
INSERT INTO orders(total, status) VALUES (1500, 'paid');
INSERT INTO orders(total, status) VALUES (2200, 'paid');
SELECT * FROM big_orders;

Первый заказ не проходит фильтр. А два других — попадают в таблицу. Без условия WHEN каждая вставка дублировалась бы в big_orders, и фильтровать пришлось бы уже при чтении.

INSTEAD OF trigger в SQLite: делаем представление записываемым

По умолчанию представления (views) в SQLite доступны только для чтения. Триггер INSTEAD OF перехватывает попытку записи в представление и вместо неё выполняет ваш SQL — как правило, превращая операцию в запись по нижележащим таблицам:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    salary REAL
);

CREATE VIEW employee_summary AS
SELECT id, first_name || ' ' || last_name AS full_name, salary
FROM employees;

CREATE TRIGGER employee_summary_insert
INSTEAD OF INSERT ON employee_summary
BEGIN
    INSERT INTO employees(first_name, last_name, salary)
    VALUES (
        substr(NEW.full_name, 1, instr(NEW.full_name, ' ') - 1),
        substr(NEW.full_name, instr(NEW.full_name, ' ') + 1),
        NEW.salary
    );
END;

INSERT INTO employee_summary(full_name, salary) VALUES ('Борис Петров', 60000);
SELECT * FROM employees;

Приложение обращается к представлению как к обычной таблице, а триггер уже сам разбирается, как разложить значение по first_name и last_name.

Список триггеров и их удаление

Триггеры хранятся в sqlite_master рядом с таблицами и индексами:

CREATE TABLE notes (id INTEGER PRIMARY KEY, body TEXT);

CREATE TRIGGER touch_note
AFTER UPDATE ON notes
BEGIN
    SELECT 1; -- тело-заглушка
END;

SELECT name, tbl_name, sql
FROM sqlite_master
WHERE type = 'trigger';

DROP TRIGGER IF EXISTS touch_note;

SELECT count(*) AS remaining
FROM sqlite_master
WHERE type = 'trigger';

DROP TRIGGER IF EXISTS name; — это безопасная форма. А если удалить таблицу, к которой привязан триггер, он удалится автоматически — предварительно подчищать ничего не нужно.

Подводные камни, о которых стоит знать

Несколько моментов, на которых обычно спотыкаются новички:

• Триггер срабатывает на каждую строку, а не на запрос целиком. Если UPDATE затронул 1000 строк — триггер выполнится 1000 раз. И если тело триггера само по себе тяжёлое, накладные расходы быстро становятся ощутимыми.
• Триггер выполняется внутри той же транзакции. Если внешний запрос откатится, изменения, сделанные триггером, тоже откатятся. Обычно это как раз то, что нужно, но имейте в виду: триггер — не способ «залогировать что бы ни случилось».
• Рекурсивные триггеры по умолчанию отключены. Триггер, изменяющий ту же таблицу, не вызовет сам себя повторно, пока вы не выставите PRAGMA recursive_triggers = ON;. Без явной необходимости лучше не трогать.
• Запись из приложения может обойти триггер только если идёт мимо базы. Пока все изменения проходят через SQLite, триггер сработает. Даже ORM, отправляющие сырой SQL пачками, триггеры запускают.
• Не размазывайте бизнес-логику по куче триггеров. С точки зрения вызывающего кода они невидимы — тому, кто потом будет разбираться, «откуда взялась эта строка», придётся гриповать sqlite_master. Используйте триггеры для сквозных задач (аудит, производные колонки, запись через представления), а остальную логику держите в коде приложения.

Реалистичный пример: журнал аудита

Соберём всё вместе — отслеживаем каждое изменение в таблице posts:

CREATE TABLE posts (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    body TEXT,
    updated_at TEXT
);

CREATE TABLE post_audit (
    post_id INTEGER,
    action TEXT,
    old_title TEXT,
    new_title TEXT,
    at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER posts_audit_update
AFTER UPDATE OF title, body ON posts
BEGIN
    UPDATE posts SET updated_at = CURRENT_TIMESTAMP WHERE id = NEW.id;
    INSERT INTO post_audit(post_id, action, old_title, new_title)
    VALUES (NEW.id, 'update', OLD.title, NEW.title);
END;

INSERT INTO posts(title, body) VALUES ('Черновик', 'первая версия');
UPDATE posts SET title = 'Опубликовано' WHERE id = 1;
SELECT id, title, updated_at FROM posts;
SELECT * FROM post_audit;

Один триггер одновременно держит updated_at в актуальном состоянии и пишет строку в журнал аудита — и всё это в одном месте. Коду приложения, который делает UPDATE, ни про то, ни про другое знать не нужно.

Что дальше: работа с JSON

Триггеры закрывают автоматизацию вокруг событий со строками. Следующая продвинутая возможность SQLite — это то, что можно хранить внутри самой строки, то есть JSON. В SQLite есть полноценный набор JSON-функций: можно запрашивать и обновлять структурированные данные, не выходя за пределы SQL. Об этом и поговорим на следующей странице.