Триггеры (Trigger) SQLite

Триггеры SQLite являются функциями обратного вызова базы данных, которые автоматически выполняются/вызываются при возникновении указанных событий базы данных. Вот основные моменты о триггерах SQLite -

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

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

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

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

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

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

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

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

Грамматика

Ниже приведена базовая грамматика создания триггера.

CREATE TRIGGER trigger_name [BEFORE|AFTER] event_name　
ON table_name
BEGIN
　-- Логика триггера...
END;

здесь:event_nameявляются событиями для вышеуказанных таблицINSERT, DELETEиUPDATEДанные базыtable_name. Вы можете указать FOR EACH ROW после имени таблицы.

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

CREATE TRIGGER trigger_name [BEFORE|AFTER] UPDATE OF column_name　
ON table_name
BEGIN
　　　-- Логика триггера здесь...
END;

Пример

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

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

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

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

Здесь, ID - это ID записи в AUDIT, EMP_ID - это 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

перечислить триггеры

Вы можете извлечь из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';

Верхние строки SQL будут выводить только одну запись, как показано ниже-

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

Удаление триггера

Вот команда DROP, которая может быть использована для удаления существующего триггера.

sqlite>　DROP　TRIGGER　trigger_name;