1. Синтаксис

create-trigger-stmt: hide

2. Описание

CREATE TRIGGER используется, чтобы добавить триггеры к схеме базы данных. Триггеры это операции по базе данных, которые автоматически выполняются, когда указанное событие базы данных имеет место.

Каждый триггер должен определить, что будет срабатывать для одной из следующих операций: DELETE, INSERT, UPDATE. Триггер срабатывает однажды для каждой строки, которая удалена, вставлена или обновленв. Если используется синтаксис "UPDATE OF column-name", то триггер будет срабатывать только, если column-name появляется на левой стороне одного из условий в пункте SET в UPDATE statement.

Из-за исторических проблем колонки, названные в "UPDATE OF", не должны на самом деле существовать в обновляемой таблице. Непризнанные имена столбцов тихо проигнорированы. Было бы более полезно, если бы SQLite в CREATE TRIGGER выдавал ошибку, если какое-либо из имен в "UPDATE OF" не будет колонками в таблице. Однако, поскольку эта проблема была обнаружена спустя многие годы после того, как SQLite был широко развернут, мы сопротивлялись решению проблемы из страха ломки унаследованных приложений.

В это время SQLite поддерживает только триггеры FOR EACH ROW, не EACH STATEMENT. Следовательно, явное определение FOR EACH ROW дополнительно. ROW EACH FOR подразумевает, что SQL-операторы, определенные в триггере, могут быть выполнены (в зависимости от пункта WHEN) для каждой вставляемой строки базы данных, обновлены или удалены запросом, заставляющим триггер сработать.

Пункт WHEN и действия триггеров могут получить доступ к вставляемым, удаляемым или обновляемым элементам строки с использованием ссылки формы "NEW.column-name" и "OLD.column-name", где column-name это имя название колонки таблицы, с которой связан триггер. Ссылки OLD и NEW могут использоваться только в триггерах на событиях, для которых они важны, следующим образом:

Если WHEN поставляется, определенные SQL-операторы выполняются только, если пункт WHEN верен. Если никакой пункт WHEN не поставляется, SQL-операторы выполняются каждый раз.

BEFORE или AFTER определяет, когда действия триггеров будут выполнены относительно вставки, модификации или удаления связанной строки. BEFORE это умолчание, когда никакое ключевое слово не присутствует.

ON CONFLICT может быть определен как часть UPDATE или INSERT в корпусе триггера. Однако, если ON CONFLICT определяется как часть запроса, заставляющего триггер запуститься, то логика обработки конфликта из внешнего заявления используется вместо этого.

Триггеры автоматически удалены, когда таблица, с которой они связаны (таблица table-name) удалена. Однако, если действия триггеров ссылаются на другие таблицы, такой триггер не удален или изменен, если те другие таблицы tables are удалены или изменены.

Триггеры удалены, используя DROP TRIGGER.

2.1. Ограничения синтаксиса на операторы UPDATE, DELETE и INSERT в триггерах

UPDATE, DELETE и INSERT в триггерах не поддерживают полный синтаксис для UPDATE, DELETE и INSERT. Следующие ограничения применяются:

• Название таблицы, которая будет изменена в UPDATE, DELETE или INSERT, должно быть неквалифицированным именем таблицы. Другими словами, нужно использовать просто "tablename", но не "database.tablename", определяя таблицу.

• Для триггеров не-TEMP таблица, которая будет изменена или запрошена, должна существовать в той же самой базе данных как таблица или представление, к которой приложен триггер. Триггеры TEMP не подчиняются правилу той-же-самой-базы-данных. Триггеру TEMP позволяют запросить или изменить любую таблицу в любой базе данных ATTACH.

• "INSERT INTO table DEFAULT VALUES" не поддерживается.

• INDEXED BY и NOT INDEXED не поддерживаются для UPDATE и DELETE.

• ORDER BY и LIMIT в UPDATE и DELETE не поддерживаются. ORDER BY и LIMIT обычно не поддерживаются для UPDATE или DELETE ни в каком контексте, но могут быть позволены для запросов верхнего уровня, используя выбор времени компиляции SQLITE_ENABLE_UPDATE_DELETE_LIMIT. Однако, тот выбор времени компиляции относится только к UPDATE и DELETE верхнего уровня, но не к UPDATE и DELETE в триггерах.

• Общее выражение таблицы не поддерживается для запросов в триггерах.

3. Триггеры INSTEAD OF

Триггеры BEFORE и AFTER вызывают работу только над обычными таблицами. Триггеры INSTEAD OF вызывают работу только с обзорами.

Если триггер INSTEAD OF INSERT существует согласно обзору, то возможно выполнить оператор INSERT для того обзора. Никакая фактическая вставка не происходит. Вместо этого управляют запросами, содержащимися в триггере. Триггеры INSTEAD OF DELETE и INSTEAD OF UPDATE вызывают работу точно так же для DELETE и UPDATE на обзорах.

Заметьте, что sqlite3_changes() и sqlite3_total_changes() не считают срабатывания триггера INSTEAD OF, но count_changes pragma считает.

4. Некоторые триггеры в качестве примера

Предположим, что потребительские записи сохранены в таблице "customers" и записи заказа сохранены в таблице "orders", следующий триггер UPDATE гарантирует, что все связанные заказы перенаправляются, когда клиент изменяет адрес:

CREATE TRIGGER update_customer_address UPDATE OF address ON customers
BEGIN
  UPDATE orders SET address = new.address WHERE customer_name = old.name;
END;

С этим установленным триггером, выполнение:

UPDATE customers SET address = '1 Main St.' WHERE name = 'Jack Jones';

заставляет следующее быть автоматически выполненным:

UPDATE orders SET address = '1 Main St.' WHERE customer_name = 'Jack Jones';

Для примера триггера INSTEAD OF рассмотрите следующую схему:

CREATE TABLE customer(cust_id INTEGER PRIMARY KEY, cust_name TEXT,
                      cust_addr TEXT);
CREATE VIEW customer_address AS
       SELECT cust_id, cust_addr FROM customer;
CREATE TRIGGER cust_addr_chng INSTEAD OF UPDATE OF cust_addr
       ON customer_address
BEGIN
  UPDATE customer SET cust_addr=NEW.cust_addr WHERE cust_id=NEW.cust_id;
END;

Со схемой выше запрос:

UPDATE customer_address SET cust_addr=$new_address WHERE cust_id=$cust_id;

Заставляет поле customer.cust_addr быть обновленным для определенного элемента, который сделал, чтобы customer.cust_id равнялся параметру $cust_id. Отметьте, как значения, назначенные обзору, сделаны доступными как поле в специальной таблице "NEW" в триггере.

5. Предостережения об использовании триггеров BEFORE

Если триггер BEFORE UPDATE или BEFORE DELETE изменяет или удаляет строку, которая должна была быть обновлена или удалена, то результат последующего обновления или удаления, не определен. Кроме того, если триггер BEFORE изменяет или удаляет строку, не определено, будут ли триггеры AFTER, которые иначе работали бы на тех строках, на самом деле работать.

Значение NEW.rowid не определено в триггере BEFORE INSERT, в котором rowid явно не установлен в целое число.

Из-за поведений, описанных выше, программисты поощряются предпочесть триггеры AFTER триггерам BEFORE.

6. Функция RAISE()

Специальная функция SQL RAISE() может использоваться в рамках программы-триггера со следующим синтаксисом:

raise-function:

Когда один из RAISE(ROLLBACK,...), RAISE(ABORT,...) или RAISE(FAIL,...) вызывают во время выполнения программы-триггера, указанный ON CONFLICT выполняется, и текущий запрос заканчивается. Код ошибки SQLITE_CONSTRAINT возвращен к приложению, наряду с указанным сообщением об ошибке.

При вызове RAISE(IGNORE) остаток от текущей программы-триггера, запрос, который вызвал триггер, и любые последующие программы-триггеры, которые будут выполнены, оставлен. Никакие изменения базы данных не отменены до прежнего уровня. Если запрос, который заставил программу-триггер выполняться, является самостоятельно частью программы-триггера, то выполнение триггера продолжается в начале следующего шага.

7. Триггеры TEMP на таблицах не-TEMP

Триггер обычно существует в той же самой базе данных как таблица, названная в ключевом слове "ON" в CREATE TRIGGER. Возможно создать TEMP TRIGGER на таблице в другой базе данных. Такой триггер будет работать только, когда изменения будут внесены в целевую таблицу приложением, которое определило триггер. Другие запросы, которые изменяют базу данных, не будут в состоянии видеть триггер TEMP и следовательно не могут управлять триггером.

Когда определение TEMP вызывается на таблице не-TEMP, важно определить базу данных, содержащую таблицу не-TEMP. Например, в следующем запросе, важно сказать "main.tab1" вместо "tab1":

CREATE TEMP TRIGGER ex1 AFTER INSERT ON main.tab1 BEGIN ...

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