Триггер в oracle что это

21) Триггеры в PL / SQL

Что такое триггер в PL / SQL?

TRIGGERS — это хранимые программы, которые запускаются механизмом Oracle автоматически, когда в таблицу выполняются такие операторы DML, как вставка, обновление, удаление или если происходят некоторые события. Код, подлежащий исключению в случае триггера, может быть определен согласно требованию. Вы можете выбрать событие, при котором должен быть запущен триггер, и время выполнения. Целью триггера является поддержание целостности информации в базе данных.

В этом уроке вы узнаете

Преимущества триггеров

Ниже приведены преимущества триггеров.

Типы триггеров в Oracle

Триггеры могут быть классифицированы на основе следующих параметров.

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

Как создать триггер

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

Синтаксис Объяснение:

: NEW и: СТАРЫЙ пункт

В триггере уровня строки триггер срабатывает для каждой связанной строки. И иногда требуется знать значение до и после оператора DML.

Oracle предоставил два условия в триггере уровня RECORD для хранения этих значений. Мы можем использовать эти предложения для ссылки на старые и новые значения внутри тела триггера.

Это предложение следует использовать на основе события DML. Ниже в таблице будет указано, какое предложение действительно для какого оператора DML (INSERT / UPDATE / DELETE).

ВМЕСТО триггера

«INSTEAD OF trigger» — это специальный тип триггера. Он используется только в триггерах DML. Он используется, когда любое событие DML происходит в сложном представлении.

Рассмотрим пример, в котором представление составлено из 3 базовых таблиц. Когда какое-либо событие DML генерируется над этим представлением, оно становится недействительным, поскольку данные взяты из 3 разных таблиц. Так что в этом используется триггер INSTEAD OF. Триггер INSTEAD OF используется для непосредственного изменения базовых таблиц вместо изменения представления для данного события.

Пример 1 : В этом примере мы собираемся создать сложное представление из двух базовых таблиц.

Затем мы увидим, как триггер INSTEAD OF используется для выполнения UPDATE оператора детализации местоположения в этом сложном представлении. Мы также увидим, как: NEW и: OLD полезны в триггерах.

Шаг 1) Создание таблицы emp и dept с соответствующими столбцами

Код Объяснение

Вывод

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

Код Объяснение

Источник

Триггеры PL/SQL уровня команд DML на примерах

Триггеры PL/SQL уровня команд DML (или просто триггеры DML) активизируются после вставки, обновления или удаления строк конкретной таблицы (рис. 1). Это самый распространенный тип триггеров, особенно часто применяемый разработчиками. Остальные триггеры используются преимущественно администраторами базы данных Oracle. В Oracle появилась возможность объединения нескольких триггеров DML в один составной триггер.

Прежде чем браться за написание триггера, необходимо ответить на следующие вопросы:

Рис. 1. Схема срабатывания триггеров DML

Основные концепции триггеров

Прежде чем переходить к синтаксису и примерам использования триггеров DML, сле­дует познакомиться с их концепциями и терминологией.

И если для таблицы определен триггер уровня записи, он будет выполнен 1000 раз.

Примеры сценариев с использованием триггеров DML

На сайте github размещены примеры сценариев, демонстрирующих работу описанных в предыдущем разделе типов триггеров.

Триггеры в транзакциях

По умолчанию триггеры DML участвуют в транзакциях, из которых они запущены. Это означает, что:

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

Создание триггера DML

Команда создания (или замены) триггера DML имеет следующий синтаксис:

Описание всех перечисленных элементов приведено в таблице.

Рассмотрим пару примеров триггеров DML.

Предложение WHEN

Предложение WHEN предназначено для уточнения условий, при которых должен вы­полняться код триггера. В приведенном далее примере с его помощью мы указываем, что основной код триггера должен быть реализован только при изменении значения столбца salary:

При использовании WHEN следует соблюдать ряд правил:

Предложение WHEN может использоваться только в триггерах уровня записи. Поместив его в триггер уровня команды, вы получите сообщение об ошибке ком­пиляции ( ORA-04077 ).

Работа с псевдозаписями NEW и OLD

При запуске триггера уровня записи ядро PL/SQL создает и заполняет две структуры данных, имеющие много общего с записями. Речь идет о псевдозаписях NEW и OLD (пре­фикс «псевдо» указывает на то, что они не обладают всеми свойствами записей PL/SQL). В псевдозаписи OLD хранятся исходные значения обрабатываемой триггером записи, а в псевдозаписи NEW — новые. Их структура идентична структуре записи, объявленной с атрибутом %ROWTYPE и создаваемой на основе таблицы, с которой связан триггер. Несколько правил, которые следует принимать во внимание при работе с псевдозапи­сями NEW и OLD :

С помощью секции REFERENCING в триггере можно менять имена псевдозаписей данных; это помогает писать самодокументированный код, ориентированный на конкретное приложение. Пример:

Идентификация команды DML в триггере

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

Пользуясь этими директивами, можно создать один триггер, который объединяет дей­ствия для нескольких операций. Пример:

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

Пример триггера DML

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

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

Для каждого изменения в таблице frame Памела хочет отслеживать состояние строки до и после изменения. Она создает простой триггер:

Но затем она решает умерить аппетит и понижает результат первого фрейма, чтобы вызвать меньше подозрений:

Но что это? Салли слышит шум в коридоре. Она теряет самообладание и пытается замести следы:

Она даже убеждается в том, что ее исправления были удалены:

Вытирая пот со лба, Салли завершает сеанс, но рассчитывает вернуться и реализовать свои планы.

Бдительная Памела подключается к базе данных и моментально обнаруживает, что пыталась сделать Салли. Для этого она выдает запрос к таблице аудита (кстати, Памела также может настроить ежечасный запуск задания через DBMS_JOB для автоматизации этой части аудита):

Применение секции WHEN

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

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

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

Использование псевдозаписей для уточнения триггеров

Памела реализовала в системе приемлемый уровень аудита; теперь ей хотелось бы сделать систему более удобной для пользователя. Самая очевидная идея — сделать так, чтобы система сама увеличивала счет во фреймах, заканчивающихся страйком или спэром, на 10. Это позволяет счетчику отслеживать счет только за последующие броски, а счет за страйк будет начисляться автоматически:

Будучи человеком пунктуальным, Памела решает добавить проверку счета в ее набор триггеров:

Теперь любая попытка ввести строку, нарушающую это условие, будет отклонена:

Однотипные триггеры

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

Эти же действия можно выполнить и с помощью трех отдельных триггеров уровня строки типа BEFORE INSERT с взаимоисключающими условиями, задаваемыми в секциях WHEN :

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

Очередность вызова триггеров

До выхода Oraclellg порядок срабатывания нескольких триггеров DML был непред­сказуемым. В рассмотренном примере он несущественен, но как показывает следующий пример, в других ситуациях могут возникнуть проблемы. Какой результат будет получен для последнего запроса?

Есть какие-нибудь предположения? Для моей базы данных результаты получились такими:

Триггер increment_by_one увеличил вставленное значение до 2, а триггер increment_by_two увеличил его до 4. Такое поведение гарантировано, потому что оно определяется на уровне самого триггера — нет необходимости полагаться на догадки и предположения. Связи последовательности триггеров можно просмотреть в представлении зависимостей словаря данных Oracle:

Ошибки при изменении таблицы

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

Казалось бы, для проверки этого условия можно использовать следующий триггер:

Тем не менее некоторые приемы помогут предотвратить выдачу этого сообщения об ошибке:

С каждым выпуском Oracle проблема ошибок изменения таблицы становится все менее актуальной, поэтому мы не станем приводить полное описание. На сайте github размещен демонстрационный сценарий mutation_zone.sql. Кроме того, в файле mutating_template.sql представлен пакет, который может послужить шаблоном для создания вашей собствен­ной реализации перевода логики уровня записей на уровень команд.

Составные триггеры

По мере создания триггеров, содержащих все больший объем бизнес-логики, становится трудно следить за тем, какие триггеры связаны с теми или иными правилами и как триг­геры взаимодействуют друг с другом. В предыдущем разделе было показано, как три типа команд DML (вставка, обновление, удаление) объединяются в одном триггере, но разве не удобно было бы разместить триггеры строк и команд вместе в одном объекте кода? В Oracle Database 11g появилась возможность использования составных триггеров для решения этой задачи. Следующий простой пример демонстрирует этот синтаксис:

Сходство с пакетами

В остальном синтаксис составных триггеров очень похож на синтаксис автономных триггеров, но не так гибок:

Правила автономных триггеров также применимы и к составным триггерам — например, значения записей ( OLD и NEW ) не могут изменяться в триггерах уровня команд.

Различия с пакетами

Итак, составные триггеры похожи на пакеты PL/SQL, но означает ли это, что они так же рабо­тают? Нет — они работают лучше! Рассмотрим следующий пример:

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

Теперь вставим одну запись:

Пока без сюрпризов. Но следующая команда INSERT выдает ошибку из-за нарушения нового первичного ключа:

Следующая команда INSERT также снова выдает ошибку первичного ключа. Но в этом как раз ничего примечательного нет — примечательно то, что глобальная переменная была снова инициализирована значением 1 без написания дополнительного кода. Команда DML завершилась, составной триггер вышел из области действия, и со следующей командой все начинается заново:

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

FOLLOWS с составными триггерами

Составные триггеры также могут использоваться с синтаксисом FOLLOWS :

Конкретные триггеры, находящиеся внутри составного триггера, не могут определяться как срабатывающие после каких-либо автономных или составных триггеров.

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

Источник

Русские Блоги

Что такое триггер

Триггер оператора

Триггер создания оператора

повышение_приложения_-ошибки (-20001, «Недостаточно прав для вставки данных в таблицу»); выдает сообщение об ошибке и запрещает вставку

Роль триггера и время срабатывания триггера

Триггеры в Oracle могут быть построены на таблицах данных или представлениях

Вы можете создать до и после триггеров для таблицы, но для представления вы не можете создать до и после триггеров

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

Несколько действий по активации

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

Предикат триггера

Существует три типа предикатов триггера: вставка, обновление, удаление

Записать журнал операций пользователя в таблицу данных

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

Строка триггера

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

Создать и использовать триггеры строк

Синтаксис для создания триггеров строк в основном совпадает с триггерами операторов. Просто нужно добавить для каждого варианта строки. Эта опция является признаком триггера строки.

Ссылки на переменные для триггеров строк

Соответствует: старая переменная: новая переменная: новая используется для ссылки на новые записи

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

Даже если инструкция Update не включает столбец student_age. Но Oracle все еще обновляет его до 0

Модификация: значение, указанное old, является недопустимым действием, и будет сообщено об ошибке.

Переменная ссылка и ссылка

Вы можете использовать ссылочное ключевое слово для указания переменных в объявлении триггера вместо: old и: new links

Порядок выполнения нескольких триггеров

Когда действие активирует несколько триггеров, эти триггеры будут срабатывать в следующем порядке: высокий уровень> низкий уровень. Табличные триггеры предшествуют триггерам строк. Если уровень совпадает, триггер, который был создан позже, чем тот, который был создан ранее

Условия запуска

Интеллектуальная рекомендация

JDBC Review.

2. Запрос всех студентов, которые избрали курс № 1

UIImageView

Источник

Триггеры PL/SQL уровня DDL на примерах: CREATE OR REPLACE TRIGGER

Создание триггера DDL

Команда создания (или замены) триггера DDL имеет следующий синтаксис:

Элементы триггера описаны в следующей таблице.

Приведем пример простого триггера, оповещающего о создании любых объектов:

Однако сообщая о создании объекта, этот триггер не уточняет, что же именно создается. Но на самом деле триггеры DDL могут предоставлять и более полную информацию, ведь триггер можно переписать и таким образом:

В этих примерах представлены два важнейших аспекта триггеров DDL: события, с ко­торыми они связываются, и атрибуты событий, которые в них доступны.

События триггеров

Список событий, которые можно связать с триггерами DDL, приведен в табл. 1. Лю­бой триггер DDL может вызываться как до ( BEFORE ), так и после ( after ) наступления указанного события.

Таблица 1. События DLL

Как и триггеры DML, триггеры DDL запускаются, когда в заданной базе данных или схеме происходят связанные с ними события. Количество типов триггеров, которые могут быть определены в базе данных или схеме, не ограничено.

Атрибутные функции

Oracle предоставляет набор функций (определенных в пакете DBMS_STANDARD ) для получения информации о причине запуска триггера DDL и других связанных с ним параметрах (например, имя удаляемой таблицы). Перечень этих атрибутных функций приведен в табл. 2, а примеры их использования — в следующих разделах.

Таблица 2. События триггеров DDL и атрибутные функции

Об атрибутных функциях необходимо дополнительно сказать следующее:

Иными словами, это вложенная таблица строк, каждая из которых может содержать до 64 символов.

Применение событий и атрибутов

Возможности триггеров DDL лучше всего продемонстрировать на примерах. Для на­чала рассмотрим триггер, который блокирует создание любых объектов базы данных:

После его создания в базе данных не удастся создать ни один объект:

Сообщение об ошибке получилось кратким и не слишком содержательным. Произошел сбой, но какой именно? Хорошо бы включить в сообщение дополнительную информа­цию, например указать тип объекта, который пытался создать пользователь:

С таким триггером попытка создать таблицу в базе данных приведет к выводу более подробного сообщения об ошибке:

Можно было бы даже реализовать эту логику в виде триггера BEFORE и воспользоваться событием ora_sysevent :

Какой столбец был изменен?

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

Попытки удаления конкретных столбцов можно проверять следующим образом:

Функции ORA_IS_DROP_COLUMN и ORA_IS_ALTER_COLUMN не обращают внимания на то, к какой таблице присоединен столбец; они работают исключи­тельно по имени столбца.

Списки, возвращаемые атрибутными функциями

Можно ли удалить неудаляемое?

При работе с подключаемыми базами данных (Oracle Database 12c и выше) можно вставить ключевое слово PLUGGABLE перед DATABASE в определении триггера. DATABASE (без PLUGGABLE ) определяет триггер на корневом уровне. В мультиарендной (multitenant) контейнерной базе данных (CDB) только пользователь, подключившийся к корневому уровню, может создать триггер для всей базы данных. PLUGGABLE DATABASE определяет триггер для подключаемой базы данных, к которой вы подключены. Триггер срабатывает каждый раз, когда любой пользователь заданной базы данных или PDB инициирует активизирующее событие.

Триггер INSTEAD OF CREATE

Oracle предоставляет несколько способов группировки (диапазоны, хеширование) и ло­гических моделей группировки (например, по первичному ключу или по уникальному ключу). Вы должны решить, какие из них должны использоваться в вашей функции группировки.

Триггеры INSTEAD OF для операций DML (вставка, обновление и удаление) будут рас­сматриваться мною далее в блоге. Эти триггеры используют некоторые элементы синтаксиса триггера INSTEAD OF CREATE для таблиц, но этим сходство между ними ограничивается.

Источник