SQLite Триггер

SQLite Триггер— это функция обратного вызова базы данных, которая автоматически выполняется/вызывается при возникновении указанного события базы данных. Ниже приведены ключевые моменты о триггерах SQLite:

  • Триггеры SQLite можно указать так, чтобы они срабатывали при DELETE, INSERT или UPDATE в определённой таблице базы данных, либо при обновлении столбцов одной или нескольких указанных таблиц.

  • SQLite поддерживает только триггеры FOR EACH ROW, а триггеров FOR EACH STATEMENT не существует. Поэтому явно указывать FOR EACH ROW необязательно.

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

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

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

  • Когда таблица, связанная с триггером, удаляется, триггер автоматически удаляется.

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

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

Синтаксис

CreateTriggerБазовый синтаксис выглядит следующим образом:

CREATE  TRIGGER trigger_name [BEFORE|AFTER] event_name 
ON table_name
BEGIN
 -- 触发器逻辑....
END;

Здесь,event_nameможет быть операцией над упомянутой таблицейtable_nameприINSERT, DELETE и UPDATEоперация базы данных. При желании можно указать FOR EACH ROW после имени таблицы.

Ниже приведён синтаксис для создания триггера для операций UPDATE на одном или нескольких указанных столбцах таблицы:

CREATE  TRIGGER trigger_name [BEFORE|AFTER] UPDATE OF column_name 
ON table_name
BEGIN
 -- 触发器逻辑....
END;

Примеры

Предположим ситуацию, в которой мы хотим вести журнал аудита для каждой записи, вставляемой в только что созданную таблицу COMPANY (если она уже существует, удалите и создайте её заново):

sqlite> CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

Чтобы вести журнал аудита, мы создадим новую таблицу с именем AUDIT. Всякий раз, когда в таблице COMPANY появляется новая запись, в неё будет вставляться сообщение журнала:

sqlite> CREATE TABLE AUDIT(
    EMP_ID INT NOT NULL,
    ENTRY_DATE TEXT NOT NULL
);

Здесь ID — это идентификатор записи AUDIT, EMP_ID — идентификатор из таблицы COMPANY, а DATE будет хранить отметку времени, когда запись была создана в COMPANY. Итак, теперь давайте создадим триггер для таблицы COMPANY следующим образом:

sqlite> CREATE TRIGGER audit_log AFTER INSERT 
ON COMPANY
BEGIN
   INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID, datetime('now'));
END;

Теперь мы начнём вставлять записи в таблицу COMPANY, что приведёт к созданию записи журнала аудита в таблице AUDIT. Поэтому давайте создадим запись в таблице COMPANY следующим образом:

sqlite> INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Paul', 32, 'California', 20000.00 );

Это создаст следующую запись в таблице COMPANY:

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0

В то же время в таблице AUDIT будет создана запись. Эта запись является результатом триггера, который мы создали для операции INSERT над таблицей COMPANY. Аналогично можно при необходимости создавать триггеры для операций UPDATE и DELETE.

EMP_ID      ENTRY_DATE
----------  -------------------
1           2013-04-05 06:26:00

Список триггеров (TRIGGERS)

Вы можете вывести список всех триггеров из таблицыsqlite_masterследующим образом:

sqlite> SELECT name FROM sqlite_master
WHERE type = 'trigger';

Приведённая выше инструкция SQLite выведет только одну запись, как показано ниже:

name
----------
audit_log

Если вы хотите вывести список триггеров для конкретной таблицы, используйте условие AND, чтобы присоединить имя таблицы, следующим образом:

sqlite> SELECT name FROM sqlite_master
WHERE type = 'trigger' AND tbl_name = 'COMPANY';

Приведённая выше инструкция SQLite выведет только одну запись, как показано ниже:

name
----------
audit_log

Удаление триггеров (TRIGGERS)

Ниже приведена команда DROP, с помощью которой можно удалить существующие триггеры:

sqlite> DROP TRIGGER trigger_name;
Другие расширения