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

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

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

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

Оракул

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

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

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

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

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

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

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

Microsoft SQL-сервер

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

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

PostgreSQL

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

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

Краткое описание:

CREATE TRIGGER name { BEFORE | AFTER } { event [ OR ... ] }
    ON TABLE [ FOR [ EACH ] { ROW | STATEMENT } ]
    EXECUTE PROCEDURE funcname ( arguments )

Жар-птица

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

Firebird не вызывает исключений изменяющихся таблиц (например, Oracle), а триггеры по умолчанию будут как вложенными, так и рекурсивными по мере необходимости (SQL Server по умолчанию допускает вложение, но не рекурсию). Триггеры Firebird используют переменные контекста NEW и OLD (не вставленные и удаленные таблицы). ,) и укажите флаги UPDATING, INSERTING и DELETING, чтобы указать текущее использование триггера.

{CREATE | RECREATE | CREATE OR ALTER} TRIGGER name FOR {table name | view name}
 [ACTIVE | INACTIVE]
 {BEFORE | AFTER}
 {INSERT [OR UPDATE] [OR DELETE] | UPDATE [OR INSERT] [OR DELETE] | DELETE [OR UPDATE] [OR INSERT] }
 [POSITION n] AS
BEGIN
 ....
END

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

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

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

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

{CREATE | RECREATE | CREATE OR ALTER} TRIGGER name
 [ACTIVE | INACTIVE] ON
 {CONNECT | DISCONNECT | TRANSACTION START | TRANSACTION COMMIT | TRANSACTION ROLLBACK}
 [POSITION n] AS
BEGIN
 .....
END

MySQL/МарияДБ

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

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

IBM DB2 ЛУВ

IBM DB2 для распределенных систем, известная как DB2 for LUW (LUW означает L inux, Unix , Windows ), поддерживает три типа триггера: «До триггера», «После триггера» и «Вместо триггера». Поддерживаются триггеры как на уровне инструкций, так и на уровне строк. Если в таблице имеется больше триггеров для одной и той же операции, то порядок срабатывания определяется данными создания триггера. Начиная с версии 9.7 IBM DB2 поддерживает автономные транзакции .

Триггер «Перед» предназначен для проверки данных и принятия решения о разрешении операции. Если исключение выдается перед триггером, операция прерывается и данные не изменяются. В DB2 триггеры «до» доступны только для чтения — вы не можете изменять данные в триггерах «до». Триггеры After предназначены для постобработки после выполнения запрошенного изменения. После триггеры могут записывать данные в таблицы и в отличие от некоторых другие базы данных вы можете записать в любую таблицу, включая таблицу, над которой работает триггер. Вместо триггеров они предназначены для записи представлений.

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

SQLite

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 condition is mandatory ]
BEGIN
   ...
END

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

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

XML-базы данных

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

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

CREATE TRIGGER "trigger3"
    BEFORE DELETE
    ON doc("auction")/site//person
    FOR EACH NODE
    DO
    {
        if (exists($WHERE//open_auction/bidder/personref/@person=$OLD/@id))
        then ( )
        else $OLD;
    }

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

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

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

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

При создании триггера, чтобы определить, является ли он уровнем оператора или строки, просто включите предложение FOR EACH ROW для уровня строки или опустите это предложение для уровня оператора. Будьте осторожны при использовании дополнительных команд INSERT / UPDATE / DELETE в триггере, поскольку рекурсия возможна триггера, вызывающая нежелательное поведение. В приведенных ниже примерах каждый триггер изменяет отдельную таблицу. Посмотрев на то, что изменяется, вы можете увидеть некоторые распространенные применения, когда используются разные типы триггеров.

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

CREATE OR REPLACE TRIGGER phone_book_audit
  AFTER UPDATE ON phone_book FOR EACH ROW
BEGIN
  INSERT INTO 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, :OLD.phone_number, :NEW.phone_number, SYSDATE);
END;

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

UPDATE phone_book SET phone_number = '111-111-1111' WHERE last_name = 'Jones';

Аудит_ID	Аудит_Изменить	F_Имя	L_Имя	Новый_номер_телефона	Старый_номер_телефона	Дата_аудита
1	Обновлять	Иордания	Джонс	111-111-1111	098-765-4321	02 МАЯ 14
2	Обновлять	Меган	Джонс	111-111-1111	111-222-3456	02 МАЯ 14

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

After — триггер уровня оператора

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

CREATE OR REPLACE TRIGGER phone_book_history
  AFTER UPDATE ON 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);
END;

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

UPDATE phone_book SET phone_number = '111-111-1111' WHERE last_name = 'Jones';

Audit_History_ID	Имя пользователя	Модификация	Edit_Date
1	ХАУШБЦ	Обновлять	02 МАЯ 14

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

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

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

CREATE OR REPLACE TRIGGER phone_book_insert
  BEFORE INSERT ON phone_book FOR EACH ROW
  WHEN (LENGTH(new.last_name) > 10)
BEGIN
    :new.last_name := SUBSTR(:new.last_name,0,1);
END;

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

INSERT INTO phone_book VALUES
(6, 'VeryVeryLongLastName', 'Erin', 'Minneapolis', 'MN', '989 University Drive', '123-222-4456', 55408, TO_DATE('11/21/1991', 'MM/DD/YYYY'));

Person_ID	Фамилия	Имя	Город	Государство_аббревиатура	Адрес	Номер телефона	Почтовый индекс	Дата рождения
6	V	Эрин	Миннеаполис	Миннесота	989 Юниверсити Драйв	123-222-4456	55408	21 НОЯБРЯ 91 г.

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

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

Использование триггера оператора BEFORE особенно полезно при обеспечении соблюдения ограничений базы данных. В этом примере показано, как применить ограничение к кому-то с именем «SOMEUSER» в таблице phone_book.

CREATE OR REPLACE TRIGGER hauschbc 
  BEFORE INSERT ON SOMEUSER.phone_book
BEGIN
    RAISE_APPLICATION_ERROR (
         num => -20050,
         msg => 'Error message goes here.');
END;

Теперь, когда «SOMEUSER» войдет в систему после попытки вставки, появится это сообщение об ошибке:

SQL Error: ORA-20050: Error message goes here.

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

Внешние ссылки

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