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

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

Триггеры в СУБД [ править ]

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

Oracle [ править ]

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

Триггеры на уровне схемы [ править ]

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

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

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

Триггеры системного уровня [ править ]

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

Microsoft SQL Server [ править ]

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

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

PostgreSQL [ править ]

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

• SQL позволяет триггерам срабатывать при обновлении определенных столбцов; Начиная с версии 9.0 PostgreSQL, эта функция также реализована в PostgreSQL.
• Стандарт допускает выполнение ряда операторов SQL, кроме SELECT , INSERT , UPDATE , таких как CREATE TABLE в качестве инициируемого действия. Это можно сделать путем создания хранимой процедуры или функции для вызова CREATE TABLE. ^[3]

Сводка:

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

Firebird [ править ]

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

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

{ СОЗДАТЬ  |  ВОССТАНОВИТЬ  |  CREATE  OR  ALTER }  TRIGGER  имя  FOR  { table  name  |  имя просмотра  } [ АКТИВНЫЙ | НЕАКТИВНО ] { ПЕРЕД | ПОСЛЕ } { ВСТАВИТЬ [ ИЛИ ОБНОВИТЬ ] [ ИЛИ УДАЛИТЬ ] | ОБНОВИТЬ [ ИЛИ ВСТАВИТЬ ] [ ИЛИ УДАЛИТЬ ] | УДАЛИТЬ [ ИЛИ                     ОБНОВЛЕНИЕ ]  [ ИЛИ  ВСТАВИТЬ ]  }  [ ПОЛОЖЕНИЕ  п ]  КАК НАЧАТЬ  .... КОНЕЦ

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

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

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

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

{ СОЗДАТЬ  |  ВОССТАНОВИТЬ  |  СОЗДАТЬ  ИЛИ  ИЗМЕНИТЬ }  Имя триггера  [ АКТИВНЫЙ | НЕАКТИВНО ] НА { СОЕДИНЕНИЕ | ОТКЛЮЧИТЬ | НАЧАЛО СДЕЛКИ | TRANSACTION COMMIT | ТРАНЗАКЦИИ ОТКАТА } [ ПОЛОЖЕНИЕ п ] КАК НАЧАТЬ ..... КОНЕЦ                    

MySQL / MariaDB [ править ]

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

Начиная с версии 8.0, они позволяют использовать триггеры DDL (язык определения данных) и триггеры DML (язык обработки данных). Они также позволяют использовать любой тип DDL-триггера (AFTER или BEFORE) для определения триггеров. Они создаются с помощью предложения CREATE TRIGGER и удаляются с помощью предложения DROP TRIGGER . Оператор, вызываемый при возникновении события, определяется после предложения FOR EACH ROW , за которым следует ключевое слово ( SET или BEGIN ), которое указывает, является ли то, что следует далее, выражением или оператором соответственно. ^[5]

IBM DB2 LUW [ править ]

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

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

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

SQLite [ править ]

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

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

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

Базы данных XML [ править ]

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

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

 CREATE  TRIGGER  "trigger3"  ПЕРЕД  УДАЛИТЬ  ПО  док ( "аукцион" ) / сайт // лицо  ДЛЯ  КАЖДОГО  УЗЛА  DO  {  если ( существует ( $ WHERE // open_auction / претендент / personref / @person = $ OLD / @id )) ,  то  (  )  else  $ OLD ;  }

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

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

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

Использование опций BEFORE и AFTER ^[7] определяет, когда будет вызван триггер. Предположим, у вас есть триггер, который вызывается при INSERT в определенную таблицу. Если ваш триггер использует опцию 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.

CREATE  ИЛИ  ЗАМЕНЫ  TRIGGER  phone_book_audit  ПОСЛЕ  ОБНОВЛЕНИЕ  ПО  phone_book  ДЛЯ  КАЖДОЙ  ROW НАЧАТЬ  ВСТАВИТЬ  INTO  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 , 'Обновить' ,  : OLD . Last_name ,  :СТАРЫЙ . first_name ,  : OLD . phone_number ,  : NEW . phone_number ,  SYSDATE ); КОНЕЦ ;

Теперь вызываем UPDATE в таблице phone_book для людей с фамилией «Джонс».

ОБНОВЛЕНИЕ  phone_book  SET  phone_number  =  '111-111-1111'  WHERE  last_name  =  'Jones' ;

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

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

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

CREATE  ИЛИ  ЗАМЕНА  TRIGGER  phone_book_history  ПОСЛЕ  ОБНОВЛЕНИЕ  ПО  phone_book НАЧАТЬ  ВСТАВИТЬ  INTO  phone_book_edit_history  ( audit_history_id ,  имя пользователя ,  модификация ,  edit_date )  ЗНАЧЕНИЕ  ( audit_history_id_sequence . NEXTVAL ,  USER , 'Обновить' ,  SYSDATE ); КОНЕЦ ;

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

ОБНОВЛЕНИЕ  phone_book  SET  phone_number  =  '111-111-1111'  WHERE  last_name  =  'Jones' ;

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

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

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

CREATE  ИЛИ  ЗАМЕНА  Триггер  phone_book_insert  ПЕРЕД  ВСТАВИТЬ  НА  phone_book  ДЛЯ  КАЖДОЙ  ROW ,  КОГДА  ( ДЛИНА ( новый . Last_name )  >  10 ) BEGIN ,  : новая . last_name  : =  SUBSTR (: новое . last_name , 0 , 1 ); КОНЕЦ ;

Теперь выполняем ВСТАВКУ кого-то с большим именем.

INSERT  INTO  phone_book  VALUES ( 6 ,  'VeryVeryLongLastName' ,  'Erin' ,  'Minneapolis' ,  'MN' ,  '989 University Drive' ,  '123-222-4456' ,  55408 ,  TO_DATE ( '21.11.1991 ' ,  ' ММ / ДД / ГГГГ ' ));

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

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

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

CREATE  OR  REPLACE  TRIGGER  hauschbc  ПЕРЕД  ВСТАВИТЬ  НА  SOMEUSER . phone_book BEGIN  RAISE_APPLICATION_ERROR  (  num  =>  - 20050 ,  msg  =>  'Здесь появляется сообщение об ошибке.' ); КОНЕЦ ;

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

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

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

Ссылки [ править ]

Внешние ссылки [ править ]

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