Привет, посетитель сайта ZametkiNaPolyah.ru! Продолжаем изучать базы данных и наше знакомство с библиотекой SQLite3. Тем в рубрике SQLite осталось не так уж и много. Вернее про SQLite можно писать очень много, долго и упорно, и всё равно часть вопросов останется не освещенной и освещенной не в полной мере. Под словосочетанием «тем осталось немного» я понимаю следующее: мы практически закончили изучать реализацию SQL в библиотеки SQLite. Задачу, которую я сам перед собой поставил, можно озвучить следующим образом: дать начинающему разработчику максимально понятное и подробное представление о языке SQL, а в качестве примера используется библиотека SQLite. В данной теме мы поговорим о том, что собой представляют триггеры в SQL на примере базы данных под управлением SQLite. Тему триггеров я не стал делить на части, поэтому запись получилось довольно объемной(более 4300 слов, поэтому пользуйтесь постраничной навигацией).
Триггеры в SQL на примере базы данных SQLite
Отсутствие деления темы SQL триггеров на части не вызвано желанием написать огромный текст, просто все разделы данной записи очень тесно связаны между собой и я не захотел разбивать эту связь, делая деление на части. Итак, поехали! По традиции небольшая аннотация к записи:
Содержание статьи:
Триггер – это особая разновидность хранимых процедур в базе данных. Особенность триггеров заключается в том, что SQL код, написанные в теле триггера, будет исполнен после того, как в базе данных произойдет какое-либо событие. События в базах данных происходят в результате выполнения DML команд или команд манипуляции данными. Если вы помните, то к командам манипуляции данными относятся: UPDATE, INSERT, DELETE и SELECT.
Команду SELECT мы не берем в расчет из-за того, что она никак не изменяет данные в базе данных, а лишь делает выборку данных. Основное назначение триггеров заключается в обеспечение целостности данных в базе данных, еще при помощи триггеров в SQL можно реализовать довольно-таки сложную бизнес-логику.
SQL код, написанный в теле триггера, будет выполнен автоматически, как только в базе данных произойдет одно из трех, указанных выше событий. Также мы можем задать самостоятельно события, по которым триггер будет срабатывать, а также SQL таблицу, для которой триггер будет срабатывать.
Для любой СУБД триггер – это в первую очередь объект базы данных, поэтому имя триггера должно быть уникальным во всей базе данных, SQLite в этом плане не исключение. У триггеров в SQL есть момент запуска. Момент запуска триггера можно разделить на два вида: BEFORE и AFTER. Момент запуска триггера AFTER говорит о том, что триггер будет запущен после выполнения какого-либо события в базе данных. Соответственно, момент запуска триггера BEFORE говорит о том, что триггер будет запущен до выполнения события в базе данных.
Мы еще поговорим про представления или VIEW в SQL, и вы узнаете, что SQLite позволяет только читать данные из VIEW, в отличии, скажем, от MySQL или Oracle. Триггеры могут быть назначены для представлений с целью расширить набор операций манипуляции данными того или иного представления. Такой вид триггеров получил название INSTEAD OF триггер.
Итак, триггеры можно разделить на три вида по их применению:
Так же мы можем разделить триггеры по типам SQL команд:
В некоторых СУБД триггер – это довольно мощное и полезное явление. Будьте аккуратны, используя триггеры, не используйте триггеры в рабочих базах данных. Перед тем, как реализовать триггер, создайте тестовую базу данных и посмотрите, что в итоге получится. Неправильный составленный триггер может навредить вашему проекту, повредив часть данных или удалив данные из базы данных.
Давайте перечислим самые распространенные функции триггеров:
Давайте приступим к рассмотрению триггеров на примере библиотеки SQLite.
Здесь мы коротко рассмотрим SQL синтаксис триггеров, реализованный в реляционных базах данных под управлением библиотеки SQLite3. Ранее мы уже говорили о том, как создать триггер, когда разбирались с командой CREATE в SQLite (у нас был раздел CREATE TRIGGER) и мы рассматривали, как удалить триггер, когда разбирались с особенностями команды DROP в SQLite3 (раздел DROP TRIGGER). Давайте повторим и дополним уже имеющуюся информацию о триггерах. Общий SQL синтаксис создания триггеров в SQLite вы можете увидеть на рисунке ниже.
Общий синтаксис создания триггера в базе данных под управлением 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
Для удаления триггера, как и для удаления таблицы из базы данных, используйте команду DROP. Далее идет ключевая фраза TRIGGER, которая сообщает SQLite о том, что вы хотите удалить триггер из базы данных, после чего вы можете сделать проверку IF EXISTS, о которой мы не раз уже говорили. И в конце указываете имя триггера или квалификатор. Как видите, удалить триггер намного проще, чем его создать.
Давайте перейдем к примерам использования триггеров в базах данных под управлением SQLite.
Итак, не забываем, что триггер создается для какой-либо конкретной таблицы и отслеживает события, происходящие с таблицей, для которой он создан. В 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.
Давайте теперь изменим наш пример, таблица останется той же, но мы изменим код триггера, только одну его часть: поменяем 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.
Ранее мы уже рассмотрели триггеры 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:
Также мы разобрались с тем, как работает триггер AFTER DELETE, думаю, вы без труда разберетесь с тем, как работает триггер BEFORE DELETE, просто заменив в примере триггера DELETE AFTER на BEFORE, поэтому демонстрировать триггер BEFORE DELETE мы здесь не будем и перейдем к другим примерам.
Теперь рассмотрим триггеры, срабатывающие при событии обновления данных: 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 идет тело триггеры модификации.
Библиотека SQLite, как и многие другие реляционные СУБД, позволяет использовать условие WHEN, которое позволяет задать область применения триггера при помощи SQL выражений. Синтаксис выражения в 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 на команды SQL, которые мы можем использовать в теле триггера.
В 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, вы найдете в следующей теме, в которой речь пойдет о представлениях.
Мы очень много говорили про обеспечение целостности данных и когда рассматривали различные ограничения СУБД SQLite, и когда говорили про нормальные формы в базе данных. Напомню, что первая нормальная форма – самое незащищенное отношение в базе данных. К тому же, первая нормальная форма – это всегда избыточность данных, плюс ко всему – это всевозможные проблемы, которые называются аномалиями. Вторая нормальная форма помогает нам избавиться от избыточности данных.
А третья нормальная форма устраняет всевозможные аномалии и различные зависимости, это всё важно знать и понимать при проектировании базы данных, но, к сожалению, ни одна в мире СУБД не знает, например, что Хемингуэй написал «Старик и море», а Исаак Ньютон – это физик, поэтому когда мы наполняем наши таблицы данными, могут возникать не очевидные ошибки с точки зрения логики реляционных баз данных, от которых не сможет защитить ни одна нормальная форма.
Например, когда мы говорили про внешние ключи, мы реализовывали связь один ко многим между таблицами, для этого мы создавали результирующую таблицу и я демонстрировал пример неудачно добавления данных, при котором получалось, что Джек Лондон написал «Войну и мир». Такие ошибки могут происходить довольно часто и их можно назвать конфликтами в базе данных.
Конфликт в базе данных не стоит путать с аномалией, потому что с точки зрения логики СУБД ничего криминального не происходит, а вот с точки зрения человека, знающего предметную область происходит существенная ошибка, которая может ввести человека в ступор.
Избавиться от подобных конфликтов мы можем при помощи триггеров и функции RAISE (). В SQLite есть специальные ключевые слова ON CONFLICT, которые используются для исключения конфликтов, происходящих во время операций манипуляции данными. О устранении конфликтов мы поговорим более подробно отдельно. Сейчас сконцентрируемся на триггерах и функции RAISE (). Отметим, что RAISE () – это специальная функция, которая используется только вместе с триггерами, ее синтаксис вы найдете на рисунке ниже.
Функция RAISE может принимать два аргумента. Первый аргумент описывает действие при возникновении конфликта:
Вообще тело триггера выполняется как транзакция, мы даже используем похожие команды, например тело триггера начинается после ключевого слова 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 мы не будем. Скажем лишь о том, что временный триггер доступен только для создавшего его пользователя и существует временный триггер всё то время, пока пользователь не разорвет сессию, либо не удалит триггер.
Отметим, что временные триггеры могут быть созданы не только для временных таблиц, но и для обычных таблиц.
Вы можете получить информацию о триггерах или посмотреть созданные триггеры в базе данных 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. Обратит внимание: в других СУБД эти команды работать не будут.
Поговорим теперь про удаление триггеров из базы данных. Вспомним общий синтаксис удаления триггера из базы данных:
[php]
DROP TRIGGER IF EXISTS db_name.trigger_name;
[/php]
Таким образом мы можем явно удалить триггер из базы данных SQLite. Неявное удаление триггеров происходит при удалении из базы данных таблицы, к которой он привязан. Если вы привязали обычный триггер к временной таблице, то можете смело его считать временным, так как он будет существовать до первого разрыва соединения с базой данных, после чего триггер будет удален, так как таблица перестанет существовать.
Спасибо за понятную и подробную информацию о триггерах, она мне очень помогла написать реферат. Спасибо)