Что такое ошибка во время выполнения триггера

I created a trigger as below:

CREATE OR REPLACE TRIGGER trigger_test
AFTER INSERT ON trigger_1
FOR EACH ROW
DECLARE
t_identifier VARCHAR2(10);
t_name VARCHAR2(20);
BEGIN
t_identifier := (:NEW.IDENTIFIER);
t_name := (:NEW.NAME);
INSERT INTO trigger_2(IDENTIFIER,NAME)VALUES(t_identifier,t_name);
COMMIT;
END;

I am trying to insert a row in trigger_1

INSERT INTO trigger_1(IDENTIFIER,NAME)
VALUES('1234567','Vijay');

It is giving me the errors:

ORA-04092: cannot COMMIT in a trigger
ORA-06512: at "LVSDBO46.TRIGGER_TEST", line 8
ORA-04088: error during execution of trigger 'LVSDBO46.TRIGGER_TEST'

Could nybody please help?

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

Отсутствие деления темы 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 команд:

• DELETE триггер. Триггер DELETE запускается при попытке удаления данных/строк из таблицы базы данных;
• UPDATE триггер. Триггер UPDATE будет запущен при попытке обновления/модификации данных в таблице базы данных;
• INSERT триггер. Триггер INSERT будет запущен в том случае, если вы попытаетесь вставить/добавить строку в таблицу базы данных.

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

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

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

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

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

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

Мы видим, что операция по созданию триггера начинается с команды 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, представлен на рисунке ниже.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Чтобы получилась такие результаты, я немного изменил код первого триггера, добавив столбец 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, сделать это проще простого:

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

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

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

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

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

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

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

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

Этот триггер делает очень простую вещь: он ограничивает количество записей в логе до двадцати одной. То есть в таблице 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 для обновления данных представления можно записать как:

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

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

Мы познакомились с основным способом применения триггера 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 – это пояснение к ошибке. Пояснение – это обычная строка, которую вы вводите с клавиатуры. По сути данное пояснение является сообщением об ошибке, которая произошла в результате операции манипуляции данными.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Так как триггеры БД довольно обширная тема, продолжаем разбирать ее далее. Давайте рассмотрим вариант, когда триггер работает с активизирующей его таблицей. Если говорить о работе триггеров таблиц, то как оказывается, триггер может обращаться не ко всем таблицам и столбцам этих таблиц. Для того, чтобы определить к каким таблицам возможен доступ, необходимо понимать, что такое изменяющиеся и ограничивающие таблицы. Изменяющиеся таблица (mutating table) — это именно та таблица, которая в данный момент модифицируется оператором DML! Для триггера это та таблица, для которой он был создан! Так же и те таблицы, которые обновляются в результате реализации или действия ссылочной целостности. Таблицы выполняющие каскадные удаления — DELETE CASCADE, так же являются изменяющимися. Ограничивающая таблица (constraining table) — это таблица, информация которой может быть считана при реализации ограничений ссылочной целостности. Вот такие трудности могут быть на пути создания триггеров БД. Давайте рассмотрим пример «неверного» триггера БД. Запишем вот такой триггер для таблицы TSTTRIG:

CREATE OR REPLACE TRIGGER ERRTRIG
	AFTER INSERT ON TSTTRIG
	FOR EACH ROW
	
BEGIN

UPDATE TSTTRIG
SET ROD = LOWER(ROD)
WHERE ID = :old.ID;

END ERRTRIG;
/

Получаем:

SQL> CREATE OR REPLACE TRIGGER ERRTRIG
  2   AFTER INSERT ON TSTTRIG
  3   FOR EACH ROW
  4   
  5  BEGIN
  6  
  7  UPDATE TSTTRIG
  8  SET ROD = LOWER(ROD)
  9  WHERE ID = :old.ID;
 10  
 11  END ERRTRIG;
 12  /

Триггер создан.

Здесь для простоты примера я произвожу модификацию столбца ROD после вставки строки. Пробуем добавить, запись в таблицу TSTTRIG:

INSERT INTO TSTTRIG (NM, ROD, INRW)
		VALUES ('BOB',  'DUMMY', TO_DATE('18-12-2004', 'DD-MM-YYYY'))
/

Получаем:

SQL> INSERT INTO TSTTRIG (NM, ROD, INRW)
  2    VALUES ('BOB',  'DUMMY', TO_DATE('18-12-2004', 'DD-MM-YYYY'))
  3  /
INSERT INTO TSTTRIG (NM, ROD, INRW)
            *
ошибка в строке 1:
ORA-04091: таблица MILLER.TSTTRIG изменяется, триггер/функция может не заметить 
это 
ORA-06512: на  "MILLER.ERRTRIG", line 5 
ORA-04088: ошибка во время выполнения триггера 'MILLER.ERRTRIG' 

Как видно сразу три типа ошибки вызывает эта операция! Но основная из них это ORA-04091! То есть для триггера MILLER.TSTTRIG таблица TSTTRIG является изменяющейся и модифицировать ее с помощью операторов DML он не может! Можете удалить триггер MILLER.TSTTRIG, чтобы он нам не мешал. Как это делать я думаю вы знаете! Итак, подведем черту.

SQL — операторы в теле триггера не могут:

1. Считывать или модифицировать информацию, любой таблицы изменяющейся в результате выполнения активизирующего оператора. В число таких таблиц входит и сама активизирующая таблица.
2. Считывать или модифицировать информацию столбца первичного ключа, уникальных столбцов и столбцов внешних ключей таблицы, являющейся ограничивающей по отношению к изменяющейся таблице. (уфф .. запутанная формулировочка! Но верная!).

Заметим, что эти правила верны для строковых триггеров. Для операторных триггеров они применимы только в тех случаях, когда последние активизируются в результате выполнения операции каскадного удаления информации. Следует так же сказать, что оператор INSERT воздействующий только на одну строку (хм .. ну естественно он же ее добавляет — ведь так? ) для строковых триггеров BEFORE и AFTER работающих с этой строкой активизирующая таблица как ни странно не является изменяющейся! Это единственная ситуация когда строковый триггер может считывать или модифицировать информацию активизирующей таблицы. Но! Для таких операторов как:

INSERT INTO таблица SELECT ......

активизирующая таблица всегда является изменяющейся, даже если в подзапросе возвращается только одна строка! Вот так бывает много разных ситуаций, про которые не следует забывать, а просто применять их на практике при создании триггеров для таблиц. А, что если вам все же необходимо получать данные в триггере от самой инициирующей таблицы? Тогда можно поступить следующим образом. Создать два триггера, один операторный другой строчный и производить все необходимые действия с их помощью. Давайте попробуем это проиллюстрировать, но здесь вам нужно будет кое-что вспомнить, если забыли обращайтесь к прошлым шагам там все есть! Итак, начнем с написания такого пакета:

CREATE OR REPLACE PACKAGE TrigTest IS

	TYPE m_ID IS TABLE OF TSTTRIG.ID%TYPE
	INDEX BY BINARY_INTEGER;
	
	TYPE m_NM IS TABLE OF TSTTRIG.NM%TYPE
	INDEX BY BINARY_INTEGER;
	
	V_m_ID m_ID;
	V_m_NM m_NM;
	V_num BINARY_INTEGER := 0;

END	TrigTest;
/

Получаем:

SQL> CREATE OR REPLACE PACKAGE TrigTest IS
  2  
  3   TYPE m_ID IS TABLE OF TSTTRIG.ID%TYPE
  4   INDEX BY BINARY_INTEGER;
  5  
  6   TYPE m_NM IS TABLE OF TSTTRIG.NM%TYPE
  7   INDEX BY BINARY_INTEGER;
  8  
  9   V_m_ID m_ID;
 10   V_m_NM m_NM;
 11   V_num BINARY_INTEGER := 0;
 12  
 13  END TrigTest;
 14  /

Пакет создан.

Здесь мы создали две таблицы V_m_ID и V_m_NM и переменную V_num для обращения к записям таблиц. Все достаточно не сложно! Теперь создадим такой строчный триггер:

CREATE OR REPLACE TRIGGER FIXTRG
	BEFORE INSERT OR UPDATE OF NM ON TSTTRIG
	FOR EACH ROW

BEGIN

TrigTest.V_num := TrigTest.V_num +1;
TrigTest.V_m_ID(TrigTest.V_num) := :new.ID;
TrigTest.V_m_NM(TrigTest.V_num) := :new.NM;

END FIXTRG;
/

Получаем:

SQL> CREATE OR REPLACE TRIGGER FIXTRG
  2   BEFORE INSERT OR UPDATE OF NM ON TSTTRIG
  3   FOR EACH ROW
  4  
  5  BEGIN
  6  
  7  TrigTest.V_num := TrigTest.V_num +1;
  8  TrigTest.V_m_ID(TrigTest.V_num) := :new.ID;
  9  TrigTest.V_m_NM(TrigTest.V_num) := :new.NM;
 10  
 11  END FIXTRG;
 12  /

Триггер создан.

Этот триггер фиксирует значения двух полей таблицы TSTTRIG в наших пакетных таблицах V_m_ID и V_m_NM. Далее создаем вот такой операторный триггер:

CREATE OR REPLACE TRIGGER OPERTRG
	BEFORE INSERT OR UPDATE OF NM ON TSTTRIG

DECLARE

V_CNT NUMBER;
V_COUNT CONSTANT NUMBER := 10;

BEGIN

	SELECT COUNT(*) INTO V_CNT 
		   FROM TSTTRIG;
	
	IF( TrigTest.V_num = V_COUNT ) THEN
		TrigTest.V_m_ID(TrigTest.V_num) := V_CNT; 
		TrigTest.V_m_NM(TrigTest.V_num) := 'STOP';
		TrigTest.V_num := 0;
	END IF;
	

END OPERTRG;
/

Получаем:

SQL> CREATE OR REPLACE TRIGGER OPERTRG
  2   BEFORE INSERT OR UPDATE OF NM ON TSTTRIG
  3  
  4  DECLARE
  5  
  6  V_CNT NUMBER;
  7  V_COUNT CONSTANT NUMBER := 10;
  8  
  9  BEGIN
 10  
 11   SELECT COUNT(*) INTO V_CNT
 12       FROM TSTTRIG;
 13  
 14   IF( TrigTest.V_num = V_COUNT ) THEN
 15    TrigTest.V_m_ID(TrigTest.V_num) := V_CNT;
 16    TrigTest.V_m_NM(TrigTest.V_num) := 'STOP';
 17    TrigTest.V_num := 0;
 18   END IF;
 19  
 20  
 21  END OPERTRG;
 22  /

Триггер создан.

Здесь саму идею я высосал из пальца, собственно триггер ничего путного не производит, а только фиксирует количество записей в таблице TSTTRIG в пакетных таблицах (запутался совсем в таблицах)! Но здесь явно видно, что триггер OPERTRG делает запрос к инициирующей таблице и это ему сходит с рук! Что собственно и требовалось показать! Сами можете придумать что-то более путное, но моя задача состоит в том, чтобы Вы поняли как это все происходит или запутались окончательно! Но, думаю сможете все это разобрать! Теперь давайте попробуем поизменять поля таблицы TSTTRIG два три раза:

UPDATE TSTTRIG 
	SET NM = 'ALF'
WHERE NM = 'BOB'
/

UPDATE TSTTRIG 
	SET NM = 'BOB'
WHERE NM = 'ALF'
/

UPDATE TSTTRIG 
	SET NM = 'BUBER'
WHERE NM = 'SCOTT'
/

UPDATE TSTTRIG 
	SET NM = 'SCOTT'
WHERE NM = 'BUBER'
/

COMMIT
/

Получаем:

SQL> UPDATE TSTTRIG
  2   SET NM = 'ALF'
  3  WHERE NM = 'BOB'
  4  /

1 строка обновлена.

SQL> UPDATE TSTTRIG
  2   SET NM = 'BOB'
  3  WHERE NM = 'ALF'
  4  /

1 строка обновлена.

SQL> UPDATE TSTTRIG
  2   SET NM = 'BUBER'
  3  WHERE NM = 'SCOTT'
  4  /

1 строка обновлена.

SQL> UPDATE TSTTRIG
  2   SET NM = 'SCOTT'
  3  WHERE NM = 'BUBER'
  4  /

1 строка обновлена.

SQL> COMMIT
  2  /

Фиксация обновлений завершена.

А затем напишем вот такой неименованный блок и посмотрим, что он нам выдаст по части содержимого пакетных табличек:

SET SERVEROUTPUT ON

DECLARE

BEGIN 

	FOR V_LOOP IN 1..TrigTest.V_m_NM.COUNT LOOP
	
	DBMS_OUTPUT.enable;
	DBMS_OUTPUT.put_line(TO_CHAR(TrigTest.V_m_ID(V_LOOP)));
	DBMS_OUTPUT.put_line(TrigTest.V_m_NM(V_LOOP));
	
	END LOOP;
	
END;
/

Получаем:

SQL> SET SERVEROUTPUT ON

SQL> DECLARE
  2  
  3  BEGIN
  4  
  5   FOR V_LOOP IN 1..TrigTest.V_m_NM.COUNT LOOP
  6  
  7   DBMS_OUTPUT.enable;
  8   DBMS_OUTPUT.put_line(TO_CHAR(TrigTest.V_m_ID(V_LOOP)));
  9   DBMS_OUTPUT.put_line(TrigTest.V_m_NM(V_LOOP));
 10  
 11   END LOOP;
 12  
 13  END;
 14  /
8041                                                                            
ALF                                                                             
8041                                                                            
BOB                                                                             
7372                                                                            
BUBER                                                                           
7372                                                                            
SCOTT                                                                           
8041                                                                            
ALF                                                                             
8041                                                                            
BOB                                                                             
7372                                                                            
BUBER                                                                           
7372                                                                            
SCOTT                                                                           

Процедура PL/SQL успешно завершена.

Вот здесь ясно видно, что вся работа триггеров прошла успешно и больше здесь комментировать нечего! Думаю, теперь вам ясно, как избежать ошибок при написании триггеров при использовании изменяющихся таблиц и основные правила работы с ними. Если что-то не совсем ясно, пишите, буду рад ответить!