Триггер базы данных — это процедурный код , который автоматически выполняется в ответ на определенные события в определенной таблице или представлении в базе данных . Триггер в основном используется для поддержания целостности информации в базе данных. Например, когда новая запись (представляющая нового работника) добавляется в таблицу сотрудников, новые записи также должны быть созданы в таблицах налогов, отпусков и зарплат. Триггеры также могут использоваться для регистрации исторических данных, например, для отслеживания предыдущих зарплат сотрудников.
Ниже приведен ряд описаний того, как некоторые популярные СУБД поддерживают триггеры.
Помимо триггеров, которые срабатывают (и выполняют код PL/SQL ) при изменении данных, Oracle 10g поддерживает триггеры, которые срабатывают при изменении объектов уровня схемы (то есть таблиц), а также при возникновении событий входа или выхода пользователя из системы.
Четыре основных типа триггеров:
Начиная с Oracle 8i , события базы данных — входы в систему, выходы из системы, запуски — могут запускать триггеры Oracle. [1]
Список всех доступных событий срабатывания в Microsoft SQL Server для триггеров DDL доступен на сайте Microsoft Docs . [2]
Выполнение условных действий в триггерах (или тестирование данных после изменения) осуществляется посредством доступа к временным таблицам Inserted и Deleted .
Поддержка триггеров была введена в 1997 году. Следующая функциональность в SQL:2003 ранее не была реализована в PostgreSQL:
Синопсис:
CREATE TRIGGER имя { ДО | ПОСЛЕ } { событие [ ИЛИ ... ] } В ТАБЛИЦЕ [ ДЛЯ [ КАЖДОГО ] { СТРОКА | ОПЕРАТОР } ] ВЫПОЛНИТЬ ПРОЦЕДУРУ имя_функции ( аргументы ) Firebird поддерживает несколько триггеров на уровне строк, BEFORE или AFTER, INSERT, UPDATE, DELETE (или любую их комбинацию) для каждой таблицы, где они всегда «в дополнение к» изменениям таблицы по умолчанию, и порядок триггеров относительно друг друга может быть указан там, где он в противном случае был бы неоднозначным (предложение POSITION). Триггеры также могут существовать в представлениях, где они всегда «вместо» триггеров, заменяя логику обновляемого представления по умолчанию. (До версии 2.1 триггеры в представлениях, считающихся обновляемыми, запускались в дополнение к логике по умолчанию.)
Firebird не вызывает исключений, связанных с изменяющимися таблицами (как Oracle), а триггеры по умолчанию будут как вложенными, так и рекурсивными по мере необходимости (SQL Server по умолчанию допускает вложенность, но не рекурсию). Триггеры Firebird используют контекстные переменные NEW и OLD (не вставленные и удаленные таблицы) и предоставляют флаги UPDATING, INSERTING и DELETING для указания текущего использования триггера.
{ СОЗДАТЬ | ВОССТАНОВИТЬ | СОЗДАТЬ ИЛИ ИЗМЕНИТЬ } Имя ТРИГГЕРА ДЛЯ { имя таблицы | имя представления } [ АКТИВНЫЙ | НЕАКТИВНЫЙ ] { ДО | ПОСЛЕ } { ВСТАВИТЬ [ ИЛИ ОБНОВИТЬ ] [ ИЛИ УДАЛИТЬ ] | ОБНОВИТЬ [ ИЛИ ВСТАВИТЬ ] [ ИЛИ УДАЛИТЬ ] | УДАЛИТЬ [ ИЛИ ОБНОВИТЬ ] [ ИЛИ ВСТАВИТЬ ] } [ ПОЗИЦИЯ n ] КАК НАЧАТЬ .... КОНЕЦ Начиная с версии 2.1, Firebird дополнительно поддерживает следующие триггеры уровня базы данных:
Триггеры уровня базы данных могут помочь обеспечить многотабличные ограничения или эмулировать материализованные представления . Если в триггере TRANSACTION COMMIT возникает исключение, изменения, внесенные триггером до сих пор, откатываются, и клиентское приложение уведомляется, но транзакция остается активной, как будто COMMIT никогда не запрашивался; клиентское приложение может продолжать вносить изменения и повторно запрашивать COMMIT.
Синтаксис для триггеров базы данных:
{ СОЗДАТЬ | ВОССТАНОВИТЬ | СОЗДАТЬ ИЛИ ИЗМЕНИТЬ } Имя ТРИГГЕРА [ АКТИВНЫЙ | НЕАКТИВНЫЙ ] НА { ПОДКЛЮЧИТЬ | ОТКЛЮЧИТЬ | НАЧАЛО ТРАНЗАКЦИИ | ЗАВЕРШИТЬ ТРАНЗАКЦИЮ | ОТКАТ ТРАНЗАКЦИИ } [ ПОЗИЦИЯ n ] КАК НАЧАЛО ..... КОНЕЦ Ограниченная поддержка триггеров в СУБД 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 для LUW (LUW означает Linux , Unix , Windows ), поддерживает три типа триггеров: «До триггера», «После триггера» и «Вместо триггера». Поддерживаются триггеры как уровня оператора, так и уровня строки. Если для одной и той же операции в таблице имеется несколько триггеров, то порядок срабатывания определяется данными создания триггера. Начиная с версии 9.7 IBM DB2 поддерживает автономные транзакции. [6]
Триггер Before предназначен для проверки данных и принятия решения о том, следует ли разрешить операцию. Если из триггера Before выдается исключение, то операция прерывается, и данные не изменяются. В DB2 триггеры Before доступны только для чтения — вы не можете изменять данные в триггерах Before. Триггеры After предназначены для постобработки после выполнения запрошенного изменения. Триггеры After могут записывать данные в таблицы, и в отличие от некоторых [ каких? ] других баз данных вы можете записывать данные в любую таблицу, включая таблицу, с которой работает триггер. Триггеры Instead предназначены для того, чтобы сделать представления доступными для записи.
Триггеры обычно программируются на языке SQL PL .
CREATE [ TEMP | TEMPORARY ] TRIGGER [ IF NOT EXISTS ] [ database_name .] trigger_name [ BEFORE | AFTER | INSTEAD OF ] { DELETE | INSERT | UPDATE [ OF column_name [ , column_name ]...] } ON { table_name | view_name } [ FOR EACH ROW ] [ WHEN условие обязательно ] BEGIN ... END SQLite поддерживает только триггеры уровня строк, но не триггеры уровня операторов.
Обновляемые представления, которые не поддерживаются в SQLite, можно эмулировать с помощью триггеров INSTEAD OF.
Примером реализации триггеров в нереляционной базе данных может служить Sedna , которая обеспечивает поддержку триггеров на основе XQuery . Триггеры в Sedna были разработаны как аналоги триггеров SQL:2003 , но изначально основаны на языках запросов и обновлений XML ( XPath , XQuery и XML update language).
Триггер в Sedna устанавливается на любые узлы XML-документа, хранящегося в базе данных. Когда эти узлы обновляются, триггер автоматически выполняет запросы XQuery и обновления, указанные в его теле. Например, следующий триггер отменяет удаление узла person, если есть какие-либо открытые аукционы, на которые ссылается этот person:
СОЗДАТЬ ТРИГГЕР "trigger3" ПЕРЕД УДАЛЕНИЕМ В doc( "auction" )/ site // person ДЛЯ КАЖДОГО УЗЛА DO { if ( exist ( $ WHERE // open_auction / bidder / personref / @person = $ OLD / @id )) then ( ) else $ OLD ; } Чтобы понять, как работает поведение триггера, вам нужно знать два основных типа триггеров: триггеры уровня строки и триггеры уровня оператора. Различие между ними заключается в том, сколько раз выполняется код внутри триггера и в какое время.
Предположим, у вас есть триггер, который должен вызываться при обновлении определенной таблицы. Триггеры уровня строк будут выполняться один раз для каждой строки, затронутой обновлением. Важно помнить, что если команда UPDATE не затронула ни одной строки, триггер не выполнит никакого кода внутри триггера. Триггеры уровня операторов будут вызываться один раз независимо от того, сколько строк затронуто обновлением. Здесь важно отметить, что даже если команда UPDATE не затронула ни одной строки, код внутри триггера все равно будет выполнен один раз.
Использование опций BEFORE и AFTER [7] определяет, когда вызывается триггер. Предположим, у вас есть триггер, который вызывается при вставке в определенную таблицу. Если ваш триггер использует опцию BEFORE, код внутри триггера будет выполнен до того, как произойдет вставка в таблицу. Обычно триггер BEFORE используется для проверки входных значений вставки или соответствующего изменения значений. Теперь предположим, что у нас есть триггер, который вместо этого использует AFTER. Код внутри триггера выполняется после того, как вставка происходит в таблицу. Пример использования этого триггера — создание истории аудита для тех, кто делал вставки в базу данных, с отслеживанием внесенных изменений. При использовании этих опций вам нужно помнить несколько вещей. Опция 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 ДЛЯ КАЖДОЙ СТРОКИ НАЧАТЬ ВСТАВИТЬ В 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 , ' Update ' , : OLD.last_name , : OLD.first_name , : OLD.phone_number , : NEW.phone_number , SYSDATE ) ; КОНЕЦ ; Сейчас вызываю ОБНОВЛЕНИЕ в таблице телефонной книги для людей с фамилией «Джонс».
ОБНОВЛЕНИЕ телефонной_книги SET phone_number = '111-111-1111' WHERE last_name = 'Джонс' ;
Обратите внимание, что таблица phone_number_audit теперь заполнена двумя записями. Это связано с тем, что в базе данных есть две записи с фамилией «Джонс». Поскольку обновление изменило два отдельных значения строк, созданный триггер был вызван дважды: один раз после каждого изменения.
Синтаксис оператора триггера Oracle, который вызывается после UPDATE в таблице phone_book. Когда триггер вызывается, он делает вставку в таблицу phone_book_edit_history
СОЗДАТЬ ИЛИ ЗАМЕНИТЬ ТРИГГЕР phone_book_history ПОСЛЕ ОБНОВЛЕНИЯ phone_book BEGIN INSERT INTO phone_book_edit_history ( audit_history_id , username , modification , edit_date ) VALUES ( audit_history_id_sequence . nextVal , USER , 'Update' , SYSDATE ) ; КОНЕЦ ; Теперь выполняем точно такое же обновление, как в примере выше, однако на этот раз с триггером на уровне оператора.
ОБНОВЛЕНИЕ телефонной_книги SET phone_number = '111-111-1111' WHERE last_name = 'Джонс' ; Результат показывает, что триггер был вызван только один раз, хотя обновление изменило две строки.
В этом примере демонстрируется триггер BEFORE EACH ROW, который изменяет INSERT с помощью условного оператора WHEN. Если фамилия длиннее 10 букв, с помощью функции SUBSTR [9] мы изменяем значение столбца last_name на аббревиатуру.
СОЗДАТЬ ИЛИ ЗАМЕНИТЬ ТРИГГЕР phone_book_insert ПЕРЕД ВСТАВКОЙ В phone_book ДЛЯ КАЖДОЙ СТРОКИ КОГДА ( LENGTH ( new . last_name ) > 10 ) BEGIN : new . last_name : = SUBSTR (: new . last_name , 0 , 1 ); END ; Сейчас выполняю ВСТАВКУ кого-то с большим именем.
ВСТАВИТЬ В ЗНАЧЕНИЯ телефонной книги ( 6 , 'VeryVeryLongLastName' , 'Erin' , 'Minneapolis' , 'MN' , '989 University Drive' , '123-222-4456' , 55408 , TO_DATE ( '11/21/1991' , 'MM/DD/YYYY' )); Триггер сработал согласно результату выше, изменив значение INSERT перед его выполнением.
Использование триггера оператора BEFORE особенно полезно при применении ограничений базы данных. [10] Этот пример демонстрирует, как применить ограничение к кому-то с именем «SOMEUSER» в таблице phone_book.
СОЗДАТЬ ИЛИ ЗАМЕНИТЬ ТРИГГЕР hauschbc ПЕРЕД ВСТАВКОЙ В SOMEUSER . phone_book BEGIN RAISE_APPLICATION_ERROR ( num => - 20050 , msg => 'Здесь будет сообщение об ошибке.' ); END ; Теперь, когда "SOMEUSER" войдет в систему после попытки любой вставки, появится следующее сообщение об ошибке:
Ошибка SQL: ORA-20050: Здесь будет сообщение об ошибке.
Пользовательские ошибки, такие как эта, имеют ограничение на то, как может быть определена переменная num. Из-за многочисленных других предопределенных ошибок эта переменная должна быть в диапазоне от −20000 до −20999.
[...] триггеры системного уровня [...] были введены в Oracle8i. [...] триггеры системного уровня срабатывают при определенных системных событиях, таких как вход в систему, выход из системы, запуск базы данных, выполнение DDL и ошибка сервера [...].