Триггер базы данных — это процедурный код , который автоматически выполняется в ответ на определенные события в определенной таблице или представлении в базе данных . Триггер в основном используется для поддержания целостности информации в базе данных. Например, когда в таблицу сотрудников добавляется новая запись (представляющая нового работника), новые записи также должны быть созданы в таблицах налогов, отпусков и зарплат. Триггеры также можно использовать для регистрации исторических данных, например, для отслеживания предыдущих зарплат сотрудников.
Ниже следует серия описаний того, как срабатывает поддержка некоторых популярных СУБД .
Помимо триггеров, которые срабатывают (и выполняют код PL/SQL ) при изменении данных, Oracle 10g поддерживает триггеры, которые срабатывают при изменении объектов уровня схемы (то есть таблиц) и при возникновении событий входа или выхода пользователя из системы.
Четыре основных типа триггеров:
В Oracle 8i события базы данных — вход в систему, выход из системы, запуск — могут запускать триггеры Oracle. [1]
Список всех доступных событий запуска в Microsoft SQL Server для триггеров DDL доступен в Microsoft Docs . [2]
Выполнение условных действий в триггерах (или тестирование данных после модификации) осуществляется посредством доступа к временным таблицам Inserted и Deleted .
Поддержка триггеров появилась в 1997 году. Следующие функции SQL:2003 ранее не были реализованы в PostgreSQL:
Краткое описание:
СОЗДАТЬ ТРИГГЕР имя { ДО | ПОСЛЕ } { событие [ ИЛИ ... ] } ON TABLE [ FOR [ EACH ] { ROW | STATEMENT } ] EXECUTE PROCEDURE имя функции ( аргументы ) Firebird поддерживает несколько триггеров на уровне строк, BEFORE или AFTER, INSERT, UPDATE, DELETE (или любую их комбинацию) для каждой таблицы, где они всегда «в дополнение к» изменениям таблицы по умолчанию, а также порядок триггеров относительно каждого другое может быть указано там, где в противном случае оно было бы неоднозначным (предложение POSITION). Триггеры также могут существовать в представлениях, где они всегда являются «вместо» триггеров, заменяя обновляемую логику представления по умолчанию. (До версии 2.1 триггеры для представлений, которые считались обновляемыми, запускались в дополнение к логике по умолчанию.)
Firebird не вызывает исключений изменяющихся таблиц (например, Oracle), а триггеры по умолчанию будут как вложенными, так и рекурсивными по мере необходимости (SQL Server по умолчанию допускает вложение, но не рекурсию). Триггеры Firebird используют контекстные переменные NEW и OLD (не вставленные и удаленные таблицы). ,) и укажите флаги UPDATING, INSERTING и DELETING, чтобы указать текущее использование триггера.
{ СОЗДАТЬ | ОТДЫХ | CREATE OR ALTER } TRIGGER name FOR { имя таблицы | просмотреть имя } [ АКТИВНО | НЕАКТИВНО ] { ДО | ПОСЛЕ } { ВСТАВИТЬ [ ИЛИ ОБНОВИТЬ ] [ ИЛИ УДАЛИТЬ ] | ОБНОВИТЬ [ ИЛИ ВСТАВИТЬ ] [ ИЛИ УДАЛИТЬ ] | УДАЛИТЬ [ ИЛИ ОБНОВИТЬ ] [ ИЛИ ВСТАВИТЬ ] } [ ПОЛОЖЕНИЕ n ] КАК НАЧАЛО .... КОНЕЦ Начиная с версии 2.1, Firebird дополнительно поддерживает следующие триггеры уровня базы данных:
Триггеры уровня базы данных могут помочь обеспечить соблюдение многотабличных ограничений или эмулировать материализованные представления . Если в триггере TRANSACTION COMMIT возникает исключение, изменения, внесенные триггером на данный момент, откатываются, и клиентское приложение уведомляется, но транзакция остается активной, как если бы COMMIT никогда не запрашивался; клиентское приложение может продолжать вносить изменения и повторно запрашивать COMMIT.
Синтаксис триггеров базы данных:
{ СОЗДАТЬ | ОТДЫХ | CREATE OR ALTER } Имя триггера [ ACTIVE | НЕАКТИВНО ] ВКЛ { ПОДКЛЮЧИТЬ | ОТКЛЮЧИТЬ | НАЧАЛО СДЕЛКИ | СОВЕРШЕНИЕ ТРАНЗАКЦИИ | ОТкат ТРАНЗАКЦИИ } [ POSITION n ] AS BEGIN ..... END Ограниченная поддержка триггеров в СУБД MySQL/MariaDB была добавлена в версии MySQL 5.0, выпущенной в 2005 году. [4]
Начиная с версии 8.0, они поддерживают триггеры DDL (язык определения данных) и триггеры DML (язык манипулирования данными). Они также позволяют использовать любой тип триггера DDL (AFTER или BEFORE) для определения триггеров. Они создаются с помощью предложения CREATE TRIGGER и удаляются с помощью предложения DROP TRIGGER . Оператор, вызываемый при возникновении события, определяется после предложения FOR EACH ROW , за которым следует ключевое слово ( SET или BEGIN ), которое указывает, является ли то, что следует, выражением или оператором соответственно. [5]
IBM DB2 для распределенных систем, известная как DB2 for LUW (LUW означает L inux, Unix , Windows ), поддерживает три типа триггера: «До триггера», «После триггера» и «Вместо триггера». Поддерживаются триггеры как на уровне инструкций, так и на уровне строк. Если в таблице имеется больше триггеров для одной и той же операции, то порядок срабатывания определяется данными создания триггера. Начиная с версии 9.7 IBM DB2 поддерживает автономные транзакции. [6]
Триггер «Перед» предназначен для проверки данных и принятия решения о разрешении операции. Если исключение выдается перед триггером, операция прерывается и данные не изменяются. В DB2 триггеры «до» доступны только для чтения — вы не можете изменять данные в триггерах «до». Триггеры After предназначены для постобработки после выполнения запрошенного изменения. После триггеры могут записывать данные в таблицы и в отличие от некоторых [ которые? ] другие базы данных, которые вы можете записать в любую таблицу, включая таблицу, на которой работает триггер. Вместо триггеров они предназначены для записи представлений.
Триггеры обычно программируются на языке SQL PL .
СОЗДАТЬ [ ТЕМП | ВРЕМЕННЫЙ ] ТРИГГЕР [ ЕСЛИ НЕ СУЩЕСТВУЕТ ] [ имя_базы данных .] имя_триггера [ ДО | ПОСЛЕ | ВМЕСТО ] { УДАЛИТЬ | _ ВСТАВИТЬ | UPDATE [ OF имя_столбца [, имя_столбца ]...] } ON { имя_таблицы | имя_представления } [ ДЛЯ КАЖДОЙ СТРОКИ ] [ КОГДА условие является обязательным ] BEGIN ... END SQLite поддерживает только триггеры уровня строки, но не триггеры уровня оператора.
Обновляемые представления, которые не поддерживаются в SQLite, можно эмулировать с помощью триггеров INSTEAD OF.
Примером реализации триггеров в нереляционной базе данных может быть Sedna , обеспечивающая поддержку триггеров на основе XQuery . Триггеры в Sedna были разработаны так, чтобы быть аналогами триггеров SQL:2003 , но изначально основаны на языках запросов и обновлений XML ( Xpath , XQuery и языке обновления XML).
Триггер в Sedna устанавливается на любых узлах XML-документа, хранящегося в базе данных. Когда эти узлы обновляются, триггер автоматически выполняет запросы XQuery и обновляет, указанные в его теле. Например, следующий триггер отменяет удаление узла человека, если есть какие-либо открытые аукционы, на которые ссылается этот человек:
СОЗДАЙТЕ ТРИГГЕР "trigger3" ПЕРЕД УДАЛЕНИЕМ НА doc( "auction" )/ site // person ДЛЯ КАЖДОГО УЗЛА DO { if ( существует ( $ WHERE // open_auction / bidder / personref / @person = $ OLD / @id )) then ( ) иначе $ СТАРЫЙ ; } Чтобы понять, как работает поведение триггеров, вам необходимо знать два основных типа триггеров; это триггеры уровня строки и оператора. Разница между ними заключается в том, сколько раз и в какое время выполняется код внутри триггера.
Предположим, у вас есть триггер, который вызывается при ОБНОВЛЕНИИ определенной таблицы. Триггеры уровня строки будут выполняться один раз для каждой строки, на которую влияет UPDATE. Важно помнить, что если команда UPDATE не затрагивает ни одну строку, триггер не будет выполнять какой-либо код внутри триггера. Триггеры уровня оператора будут вызываться один раз независимо от того, на сколько строк влияет UPDATE. Здесь важно отметить, что даже если команда UPDATE не затронула ни одну строку, код внутри триггера все равно будет выполнен один раз.
Использование опций BEFORE и AFTER [7] определяет, когда вызывается триггер. Предположим, у вас есть триггер, который вызывается при вставке в определенную таблицу. Если ваш триггер использует опцию BEFORE, код внутри триггера будет выполнен до того, как произойдет INSERT в таблицу. Обычно триггер BEFORE используется для проверки входных значений INSERT или их соответствующего изменения. Теперь предположим, что у нас есть триггер, который вместо этого использует AFTER. Код внутри триггера выполняется после того, как в таблице происходит INSERT. Примером использования этого триггера является создание истории аудита того, кто вносил в базу данных, отслеживание внесенных изменений. При использовании этих опций необходимо помнить о нескольких вещах. Опция BEFORE не позволяет изменять таблицы, поэтому проверка ввода является практическим применением. Использование триггеров AFTER позволяет изменять таблицы, например вставлять их в таблицу истории аудита.
При создании триггера, чтобы определить, является ли он уровнем оператора или строки, просто включите предложение FOR EACH ROW для уровня строки или опустите это предложение для уровня оператора. Будьте осторожны при использовании дополнительных команд INSERT / UPDATE / DELETE в триггере, поскольку возможна рекурсия триггера, вызывающая нежелательное поведение. В приведенных ниже примерах каждый триггер изменяет отдельную таблицу. Посмотрев на то, что изменяется, вы можете увидеть некоторые распространенные применения, когда используются разные типы триггеров.
Ниже приведен пример синтаксиса 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 ) VALUES ( audit_id_sequence . nextVal , ' Update ' , : OLD .last_name , : OLD .first_name , : СТАРЫЙ .номер_телефона , : НОВЫЙ .номер_телефона , SYSDATE ) ; _ КОНЕЦ ; Теперь вызываем UPDATE в таблице phone_book для людей с фамилией «Джонс».
ОБНОВЛЕНИЕ phone_book SET phone_number = '111-111-1111' WHERE last_name = 'Джонс' ;
Обратите внимание, что таблица phone_number_audit теперь заполнена двумя записями. Это связано с тем, что в базе данных есть две записи с фамилией «Джонс». Поскольку обновление изменило два отдельных значения строки, созданный триггер вызывался дважды; один раз после каждого изменения.
Триггер синтаксического оператора Oracle, который вызывается после UPDATE в таблице phone_book. Когда триггер вызывается, он вставляется в таблицу phone_book_edit_history.
СОЗДАТЬ ИЛИ ЗАМЕНИТЬ ТРИГГЕР телефон_книга_история ПОСЛЕ ОБНОВЛЕНИЯ В телефонной книге НАЧАТЬ ВСТАВИТЬ В телефон_книга_edit_history ( audit_history_id , имя пользователя , модификация , edit_date ) ЗНАЧЕНИЯ ( audit_history_id_sequence . nextVal , USER , 'Update' , SYSDATE ); КОНЕЦ ; Теперь делаем то же самое обновление, что и в приведенном выше примере, но на этот раз с триггером уровня оператора.
ОБНОВЛЕНИЕ phone_book SET phone_number = '111-111-1111' WHERE last_name = 'Джонс' ; Результат показывает, что триггер был вызван только один раз, хотя обновление изменило две строки.
В этом примере демонстрируется триггер BEFORE EACH ROW, который изменяет INSERT с использованием условия WHEN. Если фамилия больше 10 букв, с помощью функции SUBSTR [9] мы меняем значение столбца Last_name на аббревиатуру.
СОЗДАЙТЕ ИЛИ ЗАМЕНИТЕ ТРИГГЕР phone_book_insert ПЕРЕД ВСТАВКОЙ В phone_book ДЛЯ КАЖДОЙ СТРОКИ КОГДА ( ДЛИНА ( новое . фамилия ) > 10 ) НАЧАЛО : новый . последнее_имя : = SUBSTR (: новый . последнее_имя , 0 , 1 ); КОНЕЦ ; Теперь выполняем INSERT кого-то с большим именем.
ВСТАВИТЬ В ЗНАЧЕНИЯ телефонной книги ( 6 , 'VeryVeryLongLastName' , 'Erin' , 'Minneapolis' , 'MN' , '989 University Drive' , '123-222-4456' , 55408 , TO_DATE ( '21.11.1991' , ' ММ/ДД/ГГГГ' )); Триггер работал согласно приведенному выше результату, изменяя значение INSERT перед его выполнением.
Использование триггера оператора BEFORE особенно полезно при обеспечении соблюдения ограничений базы данных. [10] В этом примере показано, как применить ограничение к кому-то с именем «SOMEUSER» в таблице phone_book.
СОЗДАЙТЕ ИЛИ ЗАМЕНИТЕ TRIGGER hauschbc ПЕРЕД ВСТАВКОЙ НА SOMEUSER . phone_book BEGIN RAISE_APPLICATION_ERROR ( num => - 20050 , msg => 'Здесь будет сообщение об ошибке.' ); КОНЕЦ ; Теперь, когда «SOMEUSER» войдет в систему после попытки вставки, появится это сообщение об ошибке:
Ошибка SQL: ORA-20050: здесь находится сообщение об ошибке.
Пользовательские ошибки, подобные этой, имеют ограничение на то, как можно определить переменную num. Из-за множества других предопределенных ошибок эта переменная должна находиться в диапазоне от -20000 до -20999.
[...] триггеры системного уровня [...] были введены в Oracle8i. [...] триггеры системного уровня срабатывают при определенных системных событиях, таких как вход в систему, выход из системы, запуск базы данных, выполнение DDL и ошибка сервера [...].