Триггер базы данных - Database trigger

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

Триггеры в СУБД

Ниже следует серия описаний некоторых популярных СУБД триггеры поддержки.

Oracle

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

Триггеры на уровне схемы

• После создания
• Перед изменением
• После Alter
• Перед падением
• После падения
• Перед вставкой

Четыре основных типа триггеров:

1. Триггер на уровне строки: выполняется до или после любое значение столбца строки изменения
2. Триггер уровня столбца: выполняется до или после указанный столбец изменения
3. Для каждого типа строки: этот триггер запускается один раз для каждой строки набора результатов, на которую влияет вставка / обновление / удаление.
4. Для каждого типа оператора: этот триггер выполняется только один раз для всего набора результатов, но также срабатывает каждый раз при выполнении оператора.

Триггеры системного уровня

Из Oracle 8i, события базы данных - вход в систему, выход из системы, запуск - могут запускать триггеры Oracle.^[1]

Microsoft SQL Server

Список всех доступных событий активации в Microsoft SQL Server для триггеров DDL доступен на Документы Microsoft.^[2]

Выполнение условных действий в триггерах (или тестирование данных после модификации) осуществляется путем доступа к временному Вставлено и Удалено таблицы.

PostgreSQL

Добавлена ​​поддержка триггеров в 1997 году. Следующие функции в SQL: 2003 ранее не было реализовано в PostgreSQL:

• SQL позволяет триггерам срабатывать при обновлении определенных столбцов; Начиная с версии 9.0 PostgreSQL, эта функция также реализована в PostgreSQL.
• Стандарт позволяет выполнять ряд операторов SQL, кроме ВЫБРАТЬ, ВСТАВЛЯТЬ, ОБНОВИТЬ, например, CREATE TABLE в качестве инициированного действия. Это можно сделать путем создания хранимой процедуры или функции для вызова CREATE TABLE.^[3]

Синопсис:

СОЗДАЙТЕ СПУСКОВОЙ КРЮЧОК имя { ПЕРЕД | ПОСЛЕ } { мероприятие [ ИЛИ ЖЕ ... ] }    НА СТОЛ [ ЗА [ КАЖДЫЙ ] { РЯД | УТВЕРЖДЕНИЕ } ]    ВЫПОЛНЯТЬ ПРОЦЕДУРА имя функции ( аргументы )

Жар-птица

Жар-птица поддерживает несколько триггеров на уровне строк, BEFORE или AFTER, INSERT, UPDATE, DELETE (или любую их комбинацию) для каждой таблицы, где они всегда «в дополнение к» изменениям таблицы по умолчанию, и порядок триггеров относительно друг друга могут быть указаны там, где в противном случае это было бы неоднозначным (предложение POSITION). Триггеры также могут существовать в представлениях, где они всегда являются триггерами «вместо», заменяя логику обновляемого представления по умолчанию. (До версии 2.1 триггеры для представлений, которые считались обновляемыми, работали в дополнение к логике по умолчанию.)

Firebird не вызывает исключения изменяющихся таблиц (например, Oracle), и триггеры по умолчанию будут как вложенными, так и рекурсивными по мере необходимости (SQL Server допускает вложение, но не рекурсию по умолчанию). Триггеры Firebird используют НОВЫЕ и СТАРЫЕ контекстные переменные (не вставленные и удаленные таблицы ,) и предоставьте флаги UPDATING, INSERTING и DELETING, чтобы указать текущее использование триггера.

{СОЗДАЙТЕ | СОЗДАТЬ | СОЗДАЙТЕ ИЛИ ЖЕ ИЗМЕНИТЬ} СПУСКОВОЙ КРЮЧОК имя ЗА {стол имя | Посмотреть имя} [АКТИВНЫЙ | НЕАКТИВНЫЙ] {ПЕРЕД | ПОСЛЕ} {ВСТАВЛЯТЬ [ИЛИ ЖЕ ОБНОВИТЬ] [ИЛИ ЖЕ УДАЛИТЬ] | ОБНОВИТЬ [ИЛИ ЖЕ ВСТАВЛЯТЬ] [ИЛИ ЖЕ УДАЛИТЬ] | УДАЛИТЬ [ИЛИ ЖЕ ОБНОВИТЬ] [ИЛИ ЖЕ ВСТАВЛЯТЬ] } [ПОЗИЦИЯ п] В КАЧЕСТВЕНАЧИНАТЬ ....КОНЕЦ

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

• CONNECT (возникшие здесь исключения препятствуют завершению подключения)
• ОТКЛЮЧИТЬ
• НАЧАЛО СДЕЛКИ
• ЗАВЕРШЕНИЕ ТРАНЗАКЦИИ (возникшие здесь исключения предотвращают фиксацию транзакции или подготовку, если задействована двухфазная фиксация)
• ОТКАТ СДЕЛКИ

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

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

{СОЗДАЙТЕ | СОЗДАТЬ | СОЗДАЙТЕ ИЛИ ЖЕ ИЗМЕНИТЬ} СПУСКОВОЙ КРЮЧОК имя [АКТИВНЫЙ | НЕАКТИВНЫЙ] НА {СОЕДИНЯТЬ | ОТКЛЮЧИТЬ | СДЕЛКА НАЧНИТЕ | СДЕЛКА СОВЕРШИТЬ | СДЕЛКА ОТКАТ} [ПОЗИЦИЯ п] В КАЧЕСТВЕНАЧИНАТЬ .....КОНЕЦ

MySQL / MariaDB

Ограниченная поддержка триггеров в MySQL / MariaDB СУБД был добавлен в версию MySQL 5.0, выпущенную в 2005 году.^[4]

Начиная с версии 8.0, они позволяют использовать триггеры DDL (язык определения данных) и триггеры DML (язык обработки данных). Они также позволяют использовать любой тип DDL-триггера (AFTER или BEFORE) для определения триггеров. Они создаются с помощью предложения СОЗДАТЬ ТРИГГЕР и удален с помощью предложения КАПЕЛЬНЫЙ ТРИГГЕР. Оператор, вызываемый при возникновении события, определяется после предложения ДЛЯ КАЖДОЙ РЯДЫ, за которым следует ключевое слово (НАБОР или же НАЧИНАТЬ), который указывает, является ли то, что следует далее, выражением или утверждением соответственно.^[5]

IBM DB2 LUW

IBM DB2 для распределенных систем, известных как DB2 for LUW (LUW означает Linux, Uникс Windows) поддерживает три типа триггера: до триггера, после триггера и вместо триггера. Поддерживаются триггеры как на уровне инструкции, так и на уровне строки. Если для одной и той же операции в таблице больше триггеров, то порядок срабатывания определяется данными создания триггеров. Начиная с версии 9.7 IBM DB2 поддерживает автономные транзакции.^[6]

Перед триггером используется для проверки данных и принятия решения о разрешении операции. Если исключение выбрасывается до триггера, операция прерывается и данные не изменяются. В DB2 триггеры before доступны только для чтения - вы не можете изменять данные в триггерах до. Триггеры After предназначены для пост-обработки после выполнения запрошенного изменения. После триггеры могут записывать данные в таблицы и в отличие от некоторых^{[который? ]} другие базы данных вы можете записать в любую таблицу, включая таблицу, на которой работает триггер. Вместо триггеров предназначены для записи представлений.

Триггеры обычно программируются в SQL PL язык.

SQLite

СОЗДАЙТЕ [ТЕМП. | ВРЕМЕННЫЙ] СПУСКОВОЙ КРЮЧОК [ЕСЛИ НЕТ СУЩЕСТВУЮТ] [имя_базы_данных .] trigger_name[ПЕРЕД | ПОСЛЕ | ВМЕСТО ИЗ] {УДАЛИТЬ | ВСТАВЛЯТЬ | ОБНОВИТЬ [ИЗ имя_столбца [, имя_столбца]...]} НА {table_name | view_name}   [ЗА КАЖДЫЙ РЯД] [КОГДА условие является обязательный ]НАЧИНАТЬ   ...КОНЕЦ

SQLite поддерживает только триггеры на уровне строк, но не на уровне операторов.

Обновляемые представления, которые не поддерживаются в SQLite, можно эмулировать с помощью триггеров INSTEAD OF.

Базы данных XML

Пример реализации триггеров в нереляционной базе данных может быть Седна, который обеспечивает поддержку триггеров на основе XQuery. Триггеры в Sedna были разработаны, чтобы быть аналогом SQL: 2003 триггеры, но изначально основаны на языках запросов XML и обновлений (XPath, XQuery и язык обновления XML).

Триггер в Sedna устанавливается на любые узлы XML-документа, хранящегося в базе данных. Когда эти узлы обновляются, триггер автоматически выполняет запросы XQuery и обновления, указанные в его теле. Например, следующий триггер отменяет удаление узла человека, если есть какие-либо открытые аукционы, на которые ссылается это лицо:

 СОЗДАЙТЕ СПУСКОВОЙ КРЮЧОК "trigger3"     ПЕРЕД УДАЛИТЬ     НА док ("аукцион")/сайт//человек     ЗА КАЖДЫЙ УЗЕЛ     ДЕЛАТЬ     {        если(существуют($КУДА//open_auction/участник торгов/personref/@человек=$СТАРЫЙ/@я бы))        тогда ( )        еще $СТАРЫЙ;     }

Триггеры на уровне строк и операторов

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

Предположим, у вас есть триггер, который вызывается при ОБНОВЛЕНИИ определенной таблицы. Триггеры уровня строки будут выполняться один раз для каждой строки, на которую влияет UPDATE. Важно помнить, что если ни одна строка не затронута командой UPDATE, триггер не буду выполнить любой код в триггере. Триггеры уровня инструкции будут вызываться один раз несмотря на сколько строк затронет ОБНОВЛЕНИЕ. Здесь важно отметить, что даже если команда UPDATE не повлияла на какие-либо строки, код в триггере все равно будет выполнен один раз.

Использование опций ДО и ПОСЛЕ^[7] определить, когда вызывается триггер. Предположим, у вас есть триггер, который вызывается при выполнении INSERT для определенной таблицы. Если ваш триггер использует опцию BEFORE, код внутри триггера будет выполнен до того, как произойдет INSERT в таблицу. Обычно триггер BEFORE используется для проверки входных значений INSERT или соответствующего изменения значений. Теперь предположим, что у нас есть триггер, который вместо этого использует AFTER. Код в триггере выполняется после того, как INSERT происходит с таблицей. Пример использования этого триггера - создание журнала аудита того, кто делал вставки в базу данных, с отслеживанием внесенных изменений. При использовании этих опций вам необходимо помнить о нескольких вещах. Параметр BEFORE делает не разрешать вы можете изменять таблицы, поэтому проверка ввода имеет практическое значение. Использование триггеров AFTER позволяет изменять таблицы, например вставлять в таблицу журнала аудита.

При создании триггера, чтобы определить, является ли он уровнем оператора или строки, просто включите предложение FOR EACH ROW для уровня строки или опустите предложение для уровня оператора. Будьте осторожны при использовании дополнительных ВСТАВЛЯТЬ /ОБНОВИТЬ /УДАЛИТЬ команды в вашем триггере, потому что триггер рекурсия возможно, вызывая нежелательное поведение. В приведенных ниже примерах каждый триггер модифицирует другую таблицу. Посмотрев на то, что изменяется, вы можете увидеть некоторые общие применения, когда используются разные типы триггеров.

Ниже приведен пример синтаксиса Oracle для триггера уровня строки, который вызывается ПОСЛЕ обновления ДЛЯ КАЖДОЙ СТРОКИ. Этот триггер вызывается при обновлении базы данных телефонной книги. При вызове триггера он добавляет запись в отдельную таблицу с именем phone_book_audit. Также обратите внимание на то, что триггеры могут использовать такие объекты схемы, как последовательности,^[8] в этом примере audit_id_sequence.nexVal используется для создания уникальных первичные ключи в таблице phone_book_audit.

СОЗДАЙТЕ ИЛИ ЖЕ ЗАМЕНЯТЬ СПУСКОВОЙ КРЮЧОК phone_book_audit  ПОСЛЕ ОБНОВИТЬ НА телефонная книга ЗА КАЖДЫЙ РЯДНАЧИНАТЬ  ВСТАВЛЯТЬ В phone_book_audit     (audit_id,audit_change, audit_l_name, audit_f_name, audit_old_phone_number, audit_new_phone_number, audit_date)     ЗНАЧЕНИЯ    (audit_id_sequence.nextVal,'Обновлять', :СТАРЫЙ.фамилия, :СТАРЫЙ.имя, :СТАРЫЙ.телефонный номер, :НОВЫЙ.телефонный номер, SYSDATE);КОНЕЦ;

Теперь звоню ОБНОВИТЬ в таблице phone_book для людей с фамилией «Джонс».

ОБНОВИТЬ телефонная книга НАБОР телефонный номер = '111-111-1111' КУДА фамилия = 'Джонс';

Обратите внимание, что таблица phone_number_audit теперь заполнена двумя записями. Это связано с тем, что в базе данных есть две записи с фамилией «Джонс». Поскольку при обновлении были изменены значения двух отдельных строк, созданный триггер был вызван дважды; один раз после каждой модификации.

После - триггер на уровне оператора

Триггер оператора синтаксиса Oracle, который вызывается после UPDATE в таблице phone_book. Когда вызывается триггер, он делает вставку в таблицу phone_book_edit_history

СОЗДАЙТЕ ИЛИ ЖЕ ЗАМЕНЯТЬ СПУСКОВОЙ КРЮЧОК phone_book_history  ПОСЛЕ ОБНОВИТЬ НА телефонная книгаНАЧИНАТЬ  ВСТАВЛЯТЬ В phone_book_edit_history     (audit_history_id, имя пользователя, модификация, edit_date)     ЗНАЧЕНИЯ    (audit_history_id_sequence.nextVal, ПОЛЬЗОВАТЕЛЬ,'Обновлять', SYSDATE);КОНЕЦ;

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

ОБНОВИТЬ телефонная книга НАБОР телефонный номер = '111-111-1111' КУДА фамилия = 'Джонс';

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

Перед каждым - триггер на уровне строки

В этом примере демонстрируется триггер BEFORE EACH ROW, который изменяет INSERT с помощью условного выражения WHEN. Если фамилия больше 10 букв, с помощью функции SUBSTR^[9] мы меняем значение столбца last_name на аббревиатуру.

СОЗДАЙТЕ ИЛИ ЖЕ ЗАМЕНЯТЬ СПУСКОВОЙ КРЮЧОК phone_book_insert  ПЕРЕД ВСТАВЛЯТЬ НА телефонная книга ЗА КАЖДЫЙ РЯД  КОГДА (ДЛИНА(новый.фамилия) > 10)НАЧИНАТЬ    :новый.фамилия := SUBSTR(:новый.фамилия,0,1);КОНЕЦ;

Теперь выполняем INSERT кого-то с большим именем.

ВСТАВЛЯТЬ В телефонная книга ЗНАЧЕНИЯ(6, 'VeryVeryLongLastName', 'Эрин', 'Миннеаполис', "MN", "989 Университетский Драйв", '123-222-4456', 55408, НА СВИДАНИЕ('11/21/1991', 'ММ / ДД / ГГГГ'));

Триггер работал в соответствии с результатом выше, изменяя значение INSERT перед это было выполнено.

До - триггер на уровне оператора

Использование триггера оператора BEFORE особенно полезно при наложении ограничений базы данных.^[10] Этот пример демонстрирует, как наложить ограничение на кого-то с именем "SOMEUSER" в таблице phone_book.

СОЗДАЙТЕ ИЛИ ЖЕ ЗАМЕНЯТЬ СПУСКОВОЙ КРЮЧОК Hauschbc   ПЕРЕД ВСТАВЛЯТЬ НА НЕКОТОРЫЙ ПОЛЬЗОВАТЕЛЬ.телефонная книгаНАЧИНАТЬ    RAISE_APPLICATION_ERROR (         число => -20050,         сообщение => «Здесь отображается сообщение об ошибке».);КОНЕЦ;

Теперь, когда "SOMEUSER" вошел в систему после попытки любой INSERT, это сообщение об ошибке покажет:

Ошибка SQL: ORA-20050: здесь появляется сообщение об ошибке.

Пользовательские ошибки, такие как эта, имеют ограничение на то, как можно определить переменную num. Из-за множества других предопределенных ошибок эта переменная должна находиться в диапазоне от -20000 до -20999.

Рекомендации

внешняя ссылка

• ТРИГГЕР ОТДАВЛЕНИЯ Microsoft SQL Server
• Триггеры базы данных MySQL
• MySQL DB Create Triggers
• Оператор DB2 CREATE TRIGGER
• Oracle СОЗДАТЬ ТРИГГЕР
• PostgreSQL СОЗДАТЬ ТРИГГЕР
• Проблемы Oracle Mutating Table с DELETE CASCADE
• Язык запросов SQLite: СОЗДАТЬ ТРИГГЕР
• Документация Oracle по триггерам