СОЗДАНИЕ ТРИГГЕРА

1. Синтаксис

create-trigger-stmt: скрыть

2. Описание

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

Каждый триггер должен указать, что он будет срабатывать при одной из следующих операций: УДАЛЕНИЕ, ВСТАВКА, ОБНОВЛЕНИЕ. Триггер срабатывает один раз для каждой строки, которая удаляется, вставляется или обновляется. Если используется синтаксис "UPDATE OF имя_столбца", то триггер будет срабатывать только в том случае, если имя_столбца появляется в левой части одного из элементов в операторе SET в операторе ОБНОВЛЕНИЕ.

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

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

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

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

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

Оператор ON CONFLICT может быть указан как часть действия ОБНОВЛЕНИЕ или ВСТАВКА внутри тела триггера. Однако, если оператор ON CONFLICT указан в операторе, вызывающем срабатывание триггера, то вместо этого используется политика обработки конфликтов внешнего оператора.

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

Триггеры удаляются с помощью оператора DROP TRIGGER.

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

Операторы ОБНОВЛЕНИЕ, УДАЛЕНИЕ и ВСТАВКА внутри триггеров не поддерживают полный синтаксис операторов ОБНОВЛЕНИЕ, УДАЛЕНИЕ и ВСТАВКА. Применяются следующие ограничения:

• Имя таблицы, которая должна быть изменена в операторе ОБНОВЛЕНИЕ, УДАЛЕНИЕ или ВСТАВКА, должно быть простым именем таблицы. Другими словами, при указании таблицы необходимо использовать только "имя_таблицы", а не "база_данных.имя_таблицы".

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

• Форма оператора ВСТАВКА "INSERT INTO таблица DEFAULT VALUES" не поддерживается.

• Операторы INDEXED BY и NOT INDEXED не поддерживаются для операторов ОБНОВЛЕНИЕ и УДАЛЕНИЕ.

• Операторы ORDER BY и LIMIT в операторах ОБНОВЛЕНИЕ и УДАЛЕНИЕ не поддерживаются. ORDER BY и LIMIT обычно не поддерживаются для операторов ОБНОВЛЕНИЕ или УДАЛЕНИЕ ни в каком контексте, но могут быть включены для операторов верхнего уровня с помощью опции компиляции SQLITE_ENABLE_UPDATE_DELETE_LIMIT. Однако эта опция компиляции применима только к операторам ОБНОВЛЕНИЕ и УДАЛЕНИЕ верхнего уровня, а не к операторам ОБНОВЛЕНИЕ и УДАЛЕНИЕ внутри триггеров.

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

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, но pragma count_changes учитывает срабатывания триггеров INSTEAD OF.

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) остальная часть текущей программы триггера, оператор, вызвавший выполнение программы триггера, и любые последующие программы триггеров, которые должны были бы быть выполнены, отменяются. Изменения в базе данных не отменяются. Если оператор, вызвавший выполнение программы триггера, сам является частью программы триггера, то выполнение этой программы триггера возобновляется с начала следующего шага.

Начиная с версии 3.47.0 (2024-10-21), сообщение_ошибки может быть SQL-выражением. В более ранних версиях SQLite сообщение_ошибки должно было быть строковой литеральной.

7. Триггеры TEMP на таблицах, не являющихся TEMP

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

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

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

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

Эта страница была изменена в последний раз 09.10.2024 15:09:54 UTC