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

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

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

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

Oracle

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

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

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

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

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

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

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

Microsoft SQL Server

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

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

PostgreSQL

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

• SQL позволяет триггерам срабатывать при обновлении определенных столбцов; Начиная с версии 9.0 PostgreSQL, эта функция также реализована в 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 используют НОВЫЕ и СТАРЫЕ контекстные переменные (не вставленные и удаленные таблицы. ,) и предоставьте флаги 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 дополнительно поддерживает следующие триггеры уровня базы данных:

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

Триггеры уровня базы данных могут помочь обеспечить соблюдение многотабличных ограничений или имитировать материализованные представления . Если в триггере 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 / MariaDB

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

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

IBM DB2 LUW

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

Перед триггером используется для проверки данных и принятия решения о разрешении операции. Если исключение выбрасывается до триггера, операция прерывается и данные не изменяются. В DB2 триггеры before доступны только для чтения - вы не можете изменять данные в триггерах до. Триггеры 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 и AFTER определяет, когда будет вызван триггер. Предположим, у вас есть триггер, который вызывается при выполнении INSERT в определенную таблицу. Если ваш триггер использует опцию 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';

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

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

Триггер оператора синтаксиса 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';

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

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

В этом примере демонстрируется триггер 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 INTO phone_book VALUES
(6, 'VeryVeryLongLastName', 'Erin', 'Minneapolis', 'MN', '989 University Drive', '123-222-4456', 55408, TO_DATE('11/21/1991', 'MM/DD/YYYY'));

Триггер работал согласно приведенному выше результату, изменяя значение 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" входит в систему после попытки любой INSERT, это сообщение об ошибке будет отображаться:

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

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

использованная литература

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

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