Привет, посетитель сайта ZametkiNaPolyah.ru! Продолжаем изучать базы данных и наше знакомство с библиотекой SQLite3. Тем в рубрике SQLite осталось не так уж и много. Вернее про SQLite можно писать очень много, долго и упорно, и всё равно часть вопросов останется не освещенной и освещенной не в полной мере. Под словосочетанием «тем осталось немного» я понимаю следующее: мы практически закончили изучать реализацию SQL в библиотеки SQLite. Задачу, которую я сам перед собой поставил, можно озвучить следующим образом: дать начинающему разработчику максимально понятное и подробное представление о языке SQL, а в качестве примера используется библиотека SQLite. В данной теме мы поговорим о том, что собой представляют триггеры в SQL на примере базы данных под управлением SQLite. Тему триггеров я не стал делить на части, поэтому запись получилось довольно объемной(более 4300 слов, поэтому пользуйтесь постраничной навигацией).

Триггеры в SQL на примере базы данных SQLite

Триггеры в SQL на примере базы данных SQLite

Отсутствие деления темы SQL триггеров на части не вызвано желанием написать огромный текст, просто все разделы данной записи очень тесно связаны между собой и я не захотел разбивать эту связь, делая деление на части. Итак, поехали! По традиции небольшая аннотация к записи:

  1. Сначала мы поговорим о назначении триггеров в SQL и реляционных базах данных, попутно рассмотрев синтаксис триггеров в SQLite.
  2. Затем мы поговорим о том, как срабатывают триггеры в базах данных: до возникновения события (триггер BEFORE) и после возникновения события (триггер AFTER) и параллельно разберемся в чем между ними разница.
  3. Далее мы опишем триггеры по событиям, на которые они срабатывают. Событий у нас всего три, так как триггеры в SQLite срабатывают только на операции, которые тем или иным образом изменяют данные в таблицы: UPDATE, INSERT, DELETE.
  4. Далее мы рассмотрим, как составить уточняющее выражение WHEN для триггера.
  5. Рассмотрим особенности INSTEAD OF триггера, который позволяет реализовать команды манипуляции данными для представлений, отметим, что в SQLite представления нельзя редактировать, об этом мы поговорим в следующей теме.
  6. Также мы поговорим про устранение конфликтов и обеспечение целостности данных при помощи триггеров и специальной функции RAISE.
  7. И в завершении публикации вы узнаете о том, как получить информацию о триггерах/списков триггеров в базах данных SQLite3.Рассмотрим явное и неявное удаление триггеров из базы данных SQLite. И разберемся с некоторыми особенностями работы временных триггеров в SQLite.

Что такое триггер в контексте SQL? Использование триггеров в базах данных SQLite

Триггер – это особая разновидность хранимых процедур в базе данных. Особенность триггеров заключается в том, что SQL код, написанные в теле триггера, будет исполнен после того, как в базе данных произойдет какое-либо событие. События в базах данных происходят в результате выполнения DML команд или команд манипуляции данными. Если вы помните, то к командам манипуляции данными относятся: UPDATE, INSERT, DELETE и SELECT.

Команду SELECT мы не берем в расчет из-за того, что она никак не изменяет данные в базе данных, а лишь делает выборку данных. Основное назначение триггеров заключается в обеспечение целостности данных в базе данных, еще при помощи триггеров в SQL можно реализовать довольно-таки сложную бизнес-логику.

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

Для любой СУБД триггер – это в первую очередь объект базы данных, поэтому имя триггера должно быть уникальным во всей базе данных, SQLite в этом плане не исключение. У триггеров в SQL есть момент запуска. Момент запуска триггера можно разделить на два вида: BEFORE и AFTER. Момент запуска триггера AFTER говорит о том, что триггер будет запущен после выполнения какого-либо события в базе данных. Соответственно, момент запуска триггера BEFORE говорит о том, что триггер будет запущен до выполнения события в базе данных.

Мы еще поговорим про представления или VIEW в SQL, и вы узнаете, что SQLite позволяет только читать данные из VIEW, в отличии, скажем, от MySQL или Oracle. Триггеры могут быть назначены для представлений с целью расширить набор операций манипуляции данными того или иного представления.  Такой вид триггеров получил название INSTEAD OF триггер.

Итак, триггеры можно разделить на три вида по их применению:

  • триггер BEFORE, который срабатывает до выполнения какого-либо события в базе данных;
  • триггер AFTER, который срабатывает после выполнения события в базе данных;
  • INSTEAD OF триггер, который используется для манипуляции данными представлений.

Так же мы можем разделить триггеры по типам SQL команд:

В некоторых СУБД триггер – это довольно мощное и полезное явление. Будьте аккуратны, используя триггеры, не используйте триггеры в рабочих базах данных. Перед тем, как реализовать триггер, создайте тестовую базу данных и посмотрите, что в итоге получится. Неправильный составленный триггер может навредить вашему проекту, повредив часть данных или удалив данные из базы данных.

Давайте перечислим самые распространенные функции триггеров:

  1. Функция журнализации. Часто при помощи триггеров разработчики создают таблицы-журналы, в которых фиксируются различные изменения в базе данных. Обычно журналы создаются для фиксации изменений, которые вносят различные пользователи базы данных, таким образом можно отследить какой пользователь внес то или иное изменение в ту или иную таблицу базы данных.
  2. Функция согласования данных. Мы уже упоминали, что триггеры используются для обеспечения целостности данных в базе данных. Мы можем связать триггер с той или иной SQL командой, таким образом, чтобы триггер проверял связанные таблицы на согласованность данных, тем самым мы обезопасим данные.
  3. Функция очистки данных. Данная функция является подмножество функции из второго пункта. Например, вы выполняете каскадное удаление данных, в этом случае данные удаляются из таблиц, связанных ограничением внешнего ключа, но что если данные об одном объекте хранятся в несвязанных таблицах? В этом случае нас спасают триггеры. То же самое можно сказать и про операции каскадной модификации данных.
  4. Другие функции триггеров. К сожалению, в SQLite3 нет хранимых процедур за исключением триггеров. В тех СУБД, у которых реализованы хранимые процедуры, мы можем создавать собственные процедуры в теле триггера, которые могут выполнять операции, не связанные с изменением данных.

Давайте приступим к рассмотрению триггеров на примере библиотеки SQLite.

SQL синтаксис триггеров в базах данных SQLite

Здесь мы коротко рассмотрим SQL синтаксис триггеров, реализованный в реляционных базах данных под управлением библиотеки SQLite3. Ранее мы уже говорили о том, как создать триггер, когда разбирались с командой CREATE в SQLite (у нас был раздел CREATE TRIGGER) и мы рассматривали, как удалить триггер, когда разбирались с особенностями команды DROP в SQLite3 (раздел DROP TRIGGER). Давайте повторим и дополним уже имеющуюся информацию о триггерах. Общий SQL синтаксис создания триггеров в SQLite вы можете увидеть на рисунке ниже.

Общий синтаксис создания триггера в базе данных под управлением SQLite3

Общий синтаксис создания триггера в базе данных под управлением SQLite3

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

Далее идет модификатор TEMP или TEMPORARY, этот модификатор необязательный и его можно опускать. Временный триггер будет доступен только для того пользователя, который его создали, а существовать временный триггер будет до тех пор, пока пользователь не разорвет соединение или же пока не удалит его.

Далее мы указываем, что хотим создать триггер при помощи ключевого слова TRIGGER. Мы можем воспользоваться оператором EXISTS, чтобы проверить существует ли триггер в базе данных, перед тем как его создать. Данная проверка не является обязательно, но если вы попытаетесь создать триггер, который уже существует в базе данных, то произойдет ошибка, а программный код, отправлявший такой запрос, может быть остановлен.

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

Далее мы указываем как мы хотим, чтобы триггер работал: для VIEW – INSTEAD OF, перед выполнением SQL команды – BEFORE, после выполнения SQL операции – AFTER. После чего мы связываем триггер с той или иной командой. Обратите внимание: для всех трех команд манипуляции данными обязательным является указание таблицы или представления, для которых триггер создается, а вот для команды UPDATE можно указать еще и столбец, который будет отслеживать триггер.

Обратите внимание: мы можем создавать строковые триггеры при помощи конструкции FOR EACH ROW. Обычно триггеры создаются для какой-нибудь команды и, соответственно, выполняются по событию DELETE, UPDATE или INSERT, но мы можем сделать так, чтобы код триггера вызывался после изменения каждой строки таблицы при помощи конструкции FOR EACH ROW.

Так же стоит отметить, что выше мы говорили не совсем правду в контексте SQLite3. Многие СУБД поддерживают две разновидности триггеров: табличные и строчные. Строчные триггеры создаются при помощи конструкции FOR EACH ROW, но в SQLite нет табличных триггеров, поэтому даже если вы не укажите FOR EACH ROW явно, SQLite будет считать триггер строчным.

Также вы можете использовать уточняющую фразу WHEN, с которой мы разберемся на примере ниже. После того, как вы описали триггер, вы можете задать SQL команды, которые будут выполняться по тому или иному событию, другими словами – создать тело триггера. В теле триггера, создаваемого в базе данных SQLite, можно использовать четыре команды манипуляции данными: INSERT, UPDATE, SELECT, DELETE. Команды определения данных, команды определения доступа к данным и команды управления транзакциями в теле триггера SQLite не предусмотрены. Но нам стоит заметить,что триггеры, выполняя команды и отлавливая события сами работают так, как будто это транзакция.

Обратим внимание на то, что перечисленные команды в теле триггера поддерживают свой практически полный синтаксис. Например, вы можете составить сколь угодно сложный SQL запрос SELECT, в котором будете объединять таблицы или объединять результаты запросов. Чтобы сообщить SQLite, что тело триггера закончилось, используйте ключевое слово END.

Итак, мы разобрались с SQL синтаксисом создания триггеров, давайте посмотрим на SQL синтаксис удаления триггеров. SQL синтаксис удаления триггеров, реализованный в SQLite3, представлен на рисунке ниже.

Синтаксис удаления триггеров из базы данных SQLite

Синтаксис удаления триггеров из базы данных SQLite

Для удаления триггера, как и для удаления таблицы из базы данных, используйте команду DROP. Далее идет ключевая фраза TRIGGER, которая сообщает SQLite о том, что вы хотите удалить триггер из базы данных, после чего вы можете сделать проверку IF EXISTS, о которой мы не раз уже говорили. И в конце указываете имя триггера или квалификатор. Как видите, удалить триггер намного проще, чем его создать.

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

SQL событие BEFORE: выполнение триггера перед запросом

Итак, не забываем, что триггер создается для какой-либо конкретной таблицы и отслеживает события, происходящие с таблицей, для которой он создан. В SQLite нет табличных триггеров, а есть только триггеры строчные, то есть FOR EACH ROW триггеры, которые срабатывают при изменении каждой строки в таблице.

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

[php]

CREATE TABLE users (

id INTEGER PRIMARY KEY,

name TEXT NOT NULL,

age INTEGER NOT NULL,

address TEXT NOT NULL,

mydate TEXT NOT NULL

);

CREATE TABLE user_log (

Id_u INTEGER NOT NULL,

u_date TEXT NOT NULL

);

[/php]

Таблицы довольно простые, заметьте, вторую таблицу мы не связывали с первой при помощи ограничения внешнего ключа FOREIGN KEY, так как для наполнения второй таблицы мы будем использовать триггер. Так же для таблиц мы задали ограничения уровня столбца и ограничения уровня таблицы. Первичный ключ или PRIMARY KEY – это не только ограничение, но еще и индекс таблицы в базе данных.

Давайте напишем триггер, который будет срабатывать перед тем, как SQLite выполнит запрос:

[php]

CREATE TRIGGER my_u_log BEFORE INSERT

ON users

BEGIN

INSERT INTO user_log (id_u, u_date) VALUES (NEW.id, datetime ('now'));

END;

[/php]

Этот триггер срабатает перед тем, как новая строка будет добавлена в таблицу users. Давайте в этом убедимся:

[php]

INSERT INTO users (name, age, address, mydate)

VALUES ('Пупкин', 27, 'Адрес', datetime ('now'));

SELECT * FROM users;

id      name    age     address mydate

1       Пупкин  27      Адрес   2016-07-15 06:58:36

SELECT * FROM user_log;

Id_u    u_date

— 1      2016-07-15 06:58:36

[/php]

Мы видим, что данные в таблицу user_log были добавлены автоматически. К сожалению, поле date в данном случае не показывает, что вставка данных в таблицу user_log произошла до того, как были вставлены данные в таблицу users. Но этот факт мы можем заметить по значению столбца id_u, которое равно -1, так как SQLite3 просто не знает: какое значение будет в столбце id таблицы users.

SQL событие AFTER: выполнение триггера после запроса

Давайте теперь изменим наш пример, таблица останется той же, но мы изменим код триггера, только одну его часть: поменяем BEFORE на AFTER, чтобы посмотреть, как сработает триггер после выполнения запроса:

[php]

CREATE TRIGGER my_u_log AFTER INSERT

ON users

BEGIN

INSERT INTO user_log (id_u, u_date) VALUES (NEW.id, datetime ('now'));

END;

[/php]

Мы создали триггер (не забудьте удалить все объекты, чтобы пример работал корректно), который будет добавлять строки в таблицу user_log после того, как выполнится запрос INSERT, об этом нам говорит ключевое слово AFTER, давайте в этом убедимся:

[php]

INSERT INTO users (name, age, address, mydate)

VALUES ('Пупкин', 27, 'Адрес', datetime ('now'));

INSERT INTO users (name, age, address, mydate)

VALUES ('Сумкин', 17, 'Адрес2', datetime ('now'));

SELECT * FROM users;

id      name    age     address mydate

1       Пупкин  27      Адрес   2016-07-15 07:08:46

2       Сумкин  17      Адрес2  2016-07-15 07:08:46

SELECT * FROM user_log;

Id_u    u_date

1       2016-07-15 07:08:46

2       2016-07-15 07:08:46

[/php]

Теперь идентификаторы записываются корректно во вторую таблицу. Обратите внимание на модификатор NEW. Модификатор NEW – это ключевое слово, которое используется в теле триггера для того, чтобы сказать СУБД о том, что нужно брать новые значения (значение, которое мы добавляем в таблицу или модифицированный вариант значения). Надеюсь, что вы разобрались в разнице между BEFORE и AFTER.

INSERT триггеры и DELETE триггеры. Триггеры добавления и удаления данных

Ранее мы уже рассмотрели триггеры AFTER INSERT и BEFORE INSERT, поэтому мы не будем уделять здесь им особого внимания и сразу поговорим про триггеры BEFORE  DELETE и AFTER DELETE. Таблица users у нас останется прежней, а вот структуру таблицы user_log мы немного изменим:

[php]

CREATE TABLE user_log (

Id_u INTEGER NOT NULL,

u_date TEXT NOT NULL,

operation TEXT NOT NULL

);

[/php]

Мы добавили столбец operation, в котором будем хранить информацию о том, что мы сделали с пользователем: удалили или добавили. Давайте напишем триггер AFTER DELETE, который у нас будет срабатывать по событию удаления строки из таблицы users:

[php]

CREATE TRIGGER after_delete AFTER DELETE

ON users

BEGIN

INSERT INTO user_log (id_u, u_date, operation) VALUES (OLD.id, datetime ('now'), ‘del’);

END;

[/php]

А теперь давайте посмотрим на то, как будет работать триггер AFTER DELETE:

[php]

INSERT INTO users (name, age, address, mydate)

VALUES ('Пупкин', 27, 'Адрес', datetime ('now'));

INSERT INTO users (name, age, address, mydate)

VALUES ('Сумкин', 17, 'Адрес2', datetime ('now'));

INSERT INTO users (name, age, address, mydate)

VALUES ('Иванов', 37, 'Адрес3', datetime ('now'));

INSERT INTO users (name, age, address, mydate)

VALUES ('Петров', 47, 'Адрес4', datetime ('now'));

INSERT INTO users (name, age, address, mydate)

VALUES ('Сидоров', 57, 'Адрес5', datetime ('now'));

INSERT INTO users (name, age, address, mydate)

VALUES ('Парамонов', 7, 'Адрес6', datetime ('now'));

DELETE FROM users

WHERE id = 4;

SELECT * FROM user_log;

1|2016-07-15 07:29:28|ins

2|2016-07-15 07:29:28|ins

3|2016-07-15 07:29:28|ins

4|2016-07-15 07:29:28|ins

5|2016-07-15 07:29:28|ins

6|2016-07-15 07:29:28|ins

4|2016-07-15 07:29:28|del

SELECT * FROM users;

id      name                         age     address mydate

1       Пупкин                     27      Адрес   2016-07-15 07:29:28

2       Сумкин                     17      Адрес2  2016-07-15 07:29:28

3       Иванов                      37      Адрес3  2016-07-15 07:29:28

5       Сидоров                   57      Адрес5  2016-07-15 07:29:28

6       Парамонов             7        Адрес6  2016-07-15 07:29:28

[/php]

Чтобы получилась такие результаты, я немного изменил код первого триггера, добавив столбец operation. Но давайте обратим внимание на код триггера AFTER DELETE, в котором мы использовали модификатор OLD, модификатор OLD в SQL и SQLite используется в коде триггера для того, чтобы обратиться к старому значению или к значению, которое хранится в таблице (значение, которое будет изменено или модифицировано).

Теперь мы разобрались с модификаторами OLD и NEW:

  1. NEW позволяет обратиться к значению, которое указано в SQL запросе или же можно сказать, что это значение, которое мы хотим добавить или на которое хотим изменить.
  2. OLD позволяет обратиться к значению, которое хранится в таблице или же можно сказать, что это значение, которое мы хотим удалить или которое хотим изменить.

Также мы разобрались с тем, как работает триггер AFTER DELETE, думаю, вы без труда разберетесь с тем, как работает триггер BEFORE DELETE, просто заменив в примере триггера DELETE AFTER на BEFORE, поэтому демонстрировать триггер BEFORE DELETE мы здесь не будем и перейдем к другим примерам.

UPDATE триггеры: AFTER UPDATE и BEFORE UPDATE триггер. Триггеры модификации.

Теперь рассмотрим триггеры, срабатывающие при событии обновления данных: AFTER UPDATE триггер и BEFORE UPDATE триггер. Давайте напишем AFTER UPDATE триггер, который будет записывать в таблицу логов строки, хранящие информацию о модификации данных в таблице users, сделать это проще простого:

[php]

CREATE TRIGGER after_update AFTER UPDATE

ON users

BEGIN

INSERT INTO user_log (id_u, u_date, operation) VALUES (OLD.id, datetime ('now'), ‘upd’);

END;

[/php]

Таким образом мы создали триггер, который сработает после выполнения команды UPDATE или AFTER UPDATE триггер, давайте в этом убедимся:

[php]

UPDATE users SET name = 'Марков'

WHERE id = 6;

SELECT * FROM user_log;

Id_u    u_date  operation

1       2016-07-15 07:29:28     ins

…………………………………………………...

6       2016-07-15 07:29:28     ins

4       2016-07-15 07:29:28     del

6       2016-07-15 08:12:44     upd

SELECT * FROM users;

id      name    age     address mydate

1       Пупкин  27      Адрес   2016-07-15 07:29:28

……………………………………………………………………………...

6       Марков  7       Адрес6  2016-07-15 07:29:28

[/php]

Видим, что триггер сработал и в лог записалась информация о том, что для пользователя с id=6 вносились изменения. Думаю, вы легко разберетесь с тем, как сделать триггер BEFORE UPDATE по примеру AFTER UPDATE, чтобы вносимые изменения фиксировались до того, как они произойдут. Ничего сложно в триггере BEFORE UPDATE нет.

Обратите внимание: триггер модификации или UPDATE триггер может отслеживать изменения не только для всей таблицы, но и для какого-то конкретного столбца, чтобы указать столбец, который будет отслеживать триггер модификации, используйте следующий синтаксис:

[php]

CREATE TRIGGER trigg_name AFTER UPDATE

OF (column1, column2)

BEGIN

— тело триггера

END;

[/php]

Для такого триггера модификации вы можете использовать любое количество столбцов, имена которых указываются в круглых скобках, после ключевого слова OF идет тело триггеры модификации.

Условия срабатывания триггера WHEN. Уточняющие выражения

Библиотека SQLite, как и многие другие реляционные СУБД, позволяет использовать условие WHEN, которое позволяет задать область применения триггера при помощи SQL выражений. Синтаксис выражения в SQLite показан на рисунке ниже.

Синтаксис использования выражений в базах данных SQLite

Синтаксис использования выражений в базах данных SQLite

Как видим, мы можем задавать очень сложные выражения для условия WHEN, когда мы создаем триггер. Любая СУБД вычисляет это выражение для строки таблицы, к которой привязан триггер? и в том случае, если WHEN вернет значение TRUE, триггер будет выполнен. SQLite не будет вычислять выражение WHEN, если не будет происходить событие, инициирующее выполнение триггера.

Давайте напишем триггер с условием WHEN, который будет срабатывать, как AFTER INSERT:

[php]

CREATE TRIGGER after_insert AFTER INSERT

ON users WHEN (SELECT count (*) FROM user_log) > 21

BEGIN

DELETE FROM user_log  WHERE u_date = (SELECT min (u_date) FROM user_log);

INSERT INTO user_log (id_u, u_date, operation) VALUES (NEW.id, datetime ('now'), ‘ins’);

END;

[/php]

Этот триггер делает очень простую вещь: он ограничивает количество записей в логе до двадцати одной. То есть в таблице user_log будет храниться информация не обо всех модификациях, а только о последних, понятно, что количество записей в таблице можно регулировать.

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

Некоторые особенности триггеров в базах данных SQLite

Ранее мы говорили, что в теле триггера можно использовать любую команду манипуляции данных с довольно полным синтаксисом. Но ключевая часть выражения здесь: довольно полный синтаксис. Всё дело в том, что синтаксис команд манипуляции данными в теле триггера SQLite поддерживается не полностью. Итак, ваш триггер не будет создан/не будет работать если:

  1. В теле триггера вы не можете использовать квалификаторы для обращения к таблицам базы данных. Можно использовать только имена таблиц.
  2. Когда в деле триггера вы выполняете операцию INSERT, то вы не можете добавлять значения DEFAULT. Добавляемые значения должны быть явно указаны.
  3. Нельзя использовать ключевые слова INDEX BY и NOT INDEXED в командах UPDATE и DELETE.
  4. Клаузулы LIMIT и ORDER BY нельзя использовать с командами UPDATE и DELETE в теле триггера.

Вот такие ограничения накладывает SQLite на команды SQL, которые мы можем использовать в теле триггера.

Изменение данных VIEW в SQLite. Редактирование VIEW при помощи INSTEAD OF триггера в SQLite

В SQLite нет возможности редактировать VIEW. Напомним, что VIEW в SQL – это хранимый запрос, который СУБД выполняет, когда мы обращаемся к представлениям. Но мы можем манипулировать данными VIEW, которые хранятся в представлениях (данное выражение не совсем корректно, так как данные в представлениях не хранятся, это всего лишь результирующая таблица запроса SELECT) при помощи INSTEAD OF триггера.

Стоит заметить, что функция манипулирования данными представлений – это основная функция триггера INSTEAD OF, но не единственная. Триггер INSTEAD OF работает очень интересно, он позволяет выполнять команды INSERT, DELETE и UPDATE над представлениями, но результаты изменений, внесенных триггером INSTEAD OF, никак не отражаются на таблице/таблицах, на основе которых создано VIEW.

Общий синтаксис триггера INSTEAD OF UPDATE для обновления данных представления можно записать как:

[php]

CREATE TRIGGER trigg_name

INSTEAD OF UPDATE OF column_name ON view_name

BEGIN

— делаем команду UPDATE для таблицы, на основе которой создана VIEW

END;

[/php]

Синтаксис триггера INSTEAD OF INSERT для добавления строк в представление выглядит так:

[php]

CREATE TRIGGER trigg_name

INSTEAD OF INSERT ON view_name

BEGIN

— делаем команду INSERT для таблицы, на основе которой создана VIEW

END;

[/php]

Синтаксис триггера INSTEAD OF DELETE для удаления строк из представления выглядит так:

[php]

CREATE TRIGGER trigg_name

INSTEAD OF DELETE ON view_name

BEGIN

— делаем команду DELETE для таблицы, на основе которой создана VIEW

END;

[/php]

Мы познакомились с основным способом применения триггера INSTEAD OF в SQL и базах данных SQLite. Более подробные примеры по редактированию VIEW при помощи INSTEAD OF триггера в SQLite3, вы найдете в следующей теме, в которой речь пойдет о представлениях.

Функция RAISE () и SQL триггеры в библиотеки SQLite3. Устранение конфликтов в базе данных при помощи триггеров

Мы очень много говорили про обеспечение целостности данных и когда рассматривали различные ограничения СУБД SQLite, и когда говорили про нормальные формы в базе данных. Напомню, что первая нормальная форма – самое незащищенное отношение в базе данных. К тому же, первая нормальная форма – это всегда избыточность данных, плюс ко всему – это всевозможные проблемы, которые называются аномалиями. Вторая нормальная форма помогает нам избавиться от избыточности данных.

А третья нормальная форма устраняет всевозможные аномалии и различные зависимости, это всё важно знать и понимать при проектировании базы данных, но, к сожалению, ни одна в мире СУБД не знает, например, что Хемингуэй написал «Старик и море», а Исаак Ньютон – это физик, поэтому когда мы наполняем наши таблицы данными, могут возникать не очевидные ошибки с точки зрения логики реляционных баз данных, от которых не сможет защитить ни одна нормальная форма.

Например, когда мы говорили про внешние ключи, мы реализовывали связь один ко многим между таблицами, для этого мы создавали результирующую таблицу и я демонстрировал пример неудачно добавления данных, при котором получалось, что Джек Лондон написал «Войну и мир». Такие ошибки могут происходить довольно часто и их можно назвать конфликтами в базе данных.

Конфликт в базе данных не стоит путать с аномалией, потому что с точки зрения логики СУБД ничего криминального не происходит, а вот с точки зрения человека, знающего предметную область происходит существенная ошибка, которая может ввести человека в ступор.

Избавиться от подобных конфликтов мы можем при помощи триггеров и функции RAISE (). В SQLite есть специальные ключевые слова ON CONFLICT, которые используются для исключения конфликтов, происходящих во время операций манипуляции данными. О устранении конфликтов мы поговорим более подробно отдельно. Сейчас сконцентрируемся на триггерах и функции RAISE (). Отметим, что RAISE () – это специальная функция, которая используется только вместе с триггерами, ее синтаксис вы найдете на рисунке ниже.

Функция RAISE может принимать два аргумента. Первый аргумент описывает действие при возникновении конфликта:

  1. Значение IGNORE. Говорит SQLite3 о том, чтобы она игнорировала строку породившую конфликт и продолжала выполнение последующих операций.
  2. Значение ROLLBACK. ROLLBACK говорит о том, что SQLite должна откатить все операции к исходному состоянию при возникновении конфликта. При этом пользователь может изменить и повторить запрос. Не путайте с командой ROLLBACK, которая откатывая транзакцию
  3. Значение ABORT. Данное значение похоже на ROLLBACK, но разница в том, что оно отменяет не все выполненные ранее SQL запросы, а только тот запрос, при котором возникла конфликтная ситуация.
  4. Значение FAIL. Данное значение говорит СУБД о том, что нужно прервать выполнение текущей операции и сохранить результаты успешных операций, при этом операции, следующие за конфликтной, выполнены не будут.

Вообще тело триггера выполняется как транзакция, мы даже используем похожие команды, например тело триггера начинается после ключевого слова BEGIN, а вот начало транзакции обозначается ключевой фразой BEGIN TRANSACTION. Подтверждение транзакции- COMMIT или END, а завершение тела триггера обозначается при помощи ключевого слова END. А функция RAISE дает возможность триггеру работать, как команда SAVEPOINT.

Второй аргумент, который принимает функция триггера RAISE – это пояснение к ошибке. Пояснение – это обычная строка, которую вы вводите с клавиатуры. По сути данное пояснение является сообщением об ошибке, которая произошла в результате операции манипуляции данными.

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

[php]

PRAGMA foreign_keys=on;

CREATE TABLE books (

Id INTEGER PRIMARY KEY,

title TEXT NOT NULL,

count_page INTEGER NOT NULL CHECK (count_page >0),

price REAL CHECK (price >0)

);

CREATE TABLE auth (

id INTEGER PRIMARY KEY,

name TEXT NOT NULL,

age INTEGER  CHECK (age >16)

);

CREATE TABLE auth_book (

auth_id INTEGER NOT NULL,

books_id INTEGER NOT NULL,

FOREIGN KEY (auth_id) REFERENCES auth (id)

FOREIGN KEY (books_id) REFERENCES books (id)

);

— Сперва добавим несколько строк в таблицу книг

INSERT INTO books (id, title, count_page, price)

VALUES (1, ‘Белый клык’, 287, 300.00);

INSERT INTO books (id, title, count_page, price)

VALUES (2, ‘Война и мир’, 806, 780.00);

INSERT INTO books (id, title, count_page, price)

VALUES (3, ’12 стульев’, 516, 480.00);

— Затем добавим несколько авторов

INSERT INTO auth (id, name, age)

VALUES (1, ‘Джек Лондон’, 40);

INSERT INTO auth (id, name, age)

VALUES (2, ‘Лев Толстой’, 82);

INSERT INTO auth (id, name, age)

VALUES (3, ‘Илья Ильф’, 39);

INSERT INTO auth (id, name, age)

VALUES (4, ‘Евгений Петров’, 38);

[/php]

Мы наполнили две таблицы, но не наполнили таблицу-справочник, которая реализует связь многие ко многим. Давайте напишем триггер, который будет проверять смысл значений перед их добавлением в таблицу-справочник.

Давайте сперва реализуем триггер с использованием функции RAISE (), который будет проверять данные перед их вставкой:

[php]

CREATE TRIGGER books_result  BEFORE INSERT

ON auth_book

BEGIN

SELECT RAISE (FAIL, ‘Произошла ошибка, вы неправильно связали автора и книгу’) FROM auth_book

WHERE (NEW.auth_id = 1 AND books_id = 2) OR (NEW.auth_id = 1 AND NEW.books_id = 3) OR

(NEW.auth_id = 2 AND NEW.books_id = 3) OR (NEW.auth_id = 2 AND NEW.books_id = 3) OR

(NEW.auth_id = 3 AND NEW.books_id = 2) OR (NEW.auth_id = 3 AND NEW.books_id = 1) OR

(NEW.auth_id = 4 AND NEW.books_id = 2) OR (NEW.auth_id = 4 AND NEW.books_id = 1);

END;

[/php]

Конечно, пример не самый эффективный и не самый жизненный, функция RAISE в триггере проверяет и сравнивает значения столбцов, при этом строк у нас не очень много в таблицах. В его защиту скажу, что он хорошо демонстрирует возможности функции RAISE по обеспечению целостности данных. Ссылку NEW мы использовали, потому что нам необходимо проверять данные, которые мы хотим добавить в таблицу.

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

[php]

INSERT INTO auth_book (auth_id, books_id)

VALUES (1, 1);

INSERT INTO auth_book (auth_id, books_id)

VALUES (2, 2);

INSERT INTO auth_book (auth_id, books_id)

VALUES (3, 3);

INSERT INTO auth_book (auth_id, books_id)

VALUES (4, 3);

INSERT INTO auth_book (auth_id, books_id)

VALUES (1, 2);

Error: Произошла ошибка, вы неправильно связали автора и книгу

SELECT * FROM auth_book;

auth_id books_id

1       1

2       2

3       3

4       3

[/php]

Этот триггер срабатывает при каждой операции добавления строки в таблицу, а функция RAISE сравнивает добавляемое значение с исходным, которое мы задавали при помощи SQL операторов AND и OR в условии WHERE. Обратите внимание: и скобки, знак равно – это тоже SQL операторы.

Временные триггеры в базах данных SQLite3

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

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

Получит информацию о триггерах в базе данных SQLite

Вы можете получить информацию о триггерах или посмотреть созданные триггеры в базе данных SQLite при помощи команды SELECT, следующее предложение покажет вам все триггеры в базе данных:

[php]

SELECT name FROM sqlite_master

WHERE type = 'trigger';

[/php]

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

[php]

SELECT name FROM sqlite_master

WHERE type = 'trigger' AND tbl_name = ‘укажите_имя_таблицы';

[/php]

Таким образом вы можете получить список триггеров во всей базе данных или для конкретной таблицы в базе данных SQLite. Обратит внимание: в других СУБД эти команды работать не будут.

Удаление триггеров из базы данных SQLite

Поговорим теперь про удаление триггеров из базы данных. Вспомним общий синтаксис удаления триггера из базы данных:

[php]

DROP TRIGGER IF EXISTS db_name.trigger_name;

[/php]

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

Возможно, эти записи вам покажутся интересными


Выберете удобный для себя способ, чтобы оставить комментарий

This article has 1 comment

  1. Арина Алексеевна Reply

    Спасибо за понятную и подробную информацию о триггерах, она мне очень помогла написать реферат. Спасибо)

Leave a Comment

Ваш адрес email не будет опубликован. Обязательные поля помечены *

Loading Disqus Comments ...