Триггеры

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

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

В СУБД «Ред База Данных» различают три вида триггеров в зависимости от событий, на которые они реагируют:

DML триггеры - табличные триггеры.
Триггеры базы данных - триггеры на события базы данных.
DDL триггеры - триггеры на события изменения метаданных.

DML триггеры

DML триггеры вызываются при изменении состояния данных DML операциями: редактирование, добавление или удаление записей. Они могут быть определены и для таблиц, и для представлений.

CREATE TRIGGER <имя триггера> {
         [ACTIVE | INACTIVE]
         {BEFORE | AFTER} 
         { INSERT | UPDATE | DELETE } [OR { INSERT | UPDATE | DELETE }...]
         [POSITION <порядок срабатывания триггера>]
         ON {<имя таблицы> | <имя представления>}
AS
   [<объявление> [<объявление> ...] ]
BEGIN
   <блок операторов>
END

У DML триггера различают две фазы срабатывания BEFORE и AFTER, перед и после соответствующего события. В фазе BEFORE, если она доступна, можно изменять добавляемые или изменяемые строки, используя значения, которые были переданы в таблицу или представление. В фазе AFTER нельзя изменять значения, но можно использовать все значения, присвоенные записям триггерами на фазе BEFORE.

Также для DML триггера указывается событие, при котором он должен срабатывать (вызываться). Событиями могут быть добавление записи (INSERT), обновление записи (UPDATE) и удаление записи (DELETE).

Таким образом, существует шесть вариантов соотношения событие-фаза для таблицы (представления):

перед добавлением новой строки (BEFORE INSERT);
после добавления новой строки (AFTER INSERT);
перед изменением строки (BEFORE UPDATE);
после изменения строки (AFTER UPDATE);
перед удалением строки (BEFORE DELETE);
после удаления строки (AFTER DELETE).

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

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

Например,

CREATE TRIGGER BIU_EMPLOYEE
      BEFORE INSERT OR UPDATE
      ON EMPLOYEE
AS
BEGIN
   -- Общие действия для события
   IF (INSERTING) THEN
      -- Действия при вставке
   ELSE
      -- Действия при обновлении
END

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

Для DML триггеров существуют специфические контекстные переменные OLD.<столбец> и NEW.<столбец> для обращения с старым и новым значениям столбца. В триггерах можно обращаться к значению любого столбца таблицы до его изменения.

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

Контекстная переменная NEW в триггерах для фазы события после (AFTER) также является переменной только для чтения. Она недоступна в триггерах для события удаления данных.

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

Пусть имеется таблица PEOPLE, описывающая людей в базе данных. В этой таблице присутствует столбец COD, являющийся искусственным первичным ключом. Для получения значения этого ключа в базе данных создан генератор с именем GEN_PEOPLE. Чтобы при операции добавления новой строки в эту таблицу получать уникальное значение искусственного первичного ключа, нужно создать следующий триггер:

CREATE TRIGGER TBI_PEOPLE
   ACTIVE
   BEFORE INSERT
   ON PEOPLE
AS
BEGIN
   IF (NEW.COD IS NULL) THEN
      NEW.COD = NEXT VALUE FOR GEN_PEOPLE;
END

Триггер является активным (ACTIVE), создается для таблицы PEOPLE для фазы до (BEFORE) события добавления новой записи (INSERT).

В теле триггера проверяется, не присвоено ли уже первичному ключу какое-либо значение (стандартная проверка). Для этого используется имя столбца с префиксом NEW. После этой проверки, если первичный ключ не имеет еще никакого значения, значению первичного ключа присваивается уникальное значение, получаемое из генератора GEN_PEOPLE увеличением на единицу значения генератора.

Пример триггера, поддерживающего ссылочную целостность. Если при объявлении внешнего ключа в описании ограничения REFERENCES для операции DELETE используется вариант NO ACTION, то пользователь сам должен обеспечить отсутствие записей дочерней таблицы, ссылающихся на первичный ключ удаляемой записи.

CREATE OR ALTER TRIGGER TAD_COUNTRY
   AFTER DELETE
   ON COUNTRY 
AS BEGIN
   DELETE FROM REGION WHERE REGION.CODCOUNTRY = OLD.CODCOUNTRY;
END

Еще пример триггера, которые определенным образом логирует изменения в БД.

В демонстрационной базе данных EMPLOYEE, присутствуют таблицы EMPLOYEE, описывающая сотрудников организации, и SALARY_HISTORY, хранящая историю окладов сотрудников.

Следующий триггер вызывается после изменения (AFTER UPDATE) таблицы EMPLOYEE. В нем проверяется, не изменился ли оклад сотрудника, и если изменился, в триггере создается новая запись истории сотрудника.

CREATE TRIGGER SAVE_SALARY_CHANGE
   AFTER UPDATE
   ON EMPLOYEE
AS
BEGIN
   IF (OLD.SALARY <> NEW.SALARY) THEN
      INSERT INTO SALARY_HISTORY (EMP_NO, CHANGE_DATE, UPDATER_ID, 
               OLD_SALARY, 
               PERCENT_CHANGE)
            VALUES (OLD.EMP_NO, 'NOW', USER, 
               OLD.SALARY, 
               (NEW.SALARY - OLD.SALARY) * 100 / OLD.SALARY);
END

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

CREATE TRIGGER <имя триггера> {
         [ACTIVE | INACTIVE]
         ON {CONNECT | DISCONNECT | TRANSACTION START | TRANSACTION COMMIT | TRANSACTION ROLLBACK}
         [POSITION <порядок срабатывания триггера>]
AS
   [<объявление> [<объявление> ...] ]
BEGIN
   <блок операторов>
END

Триггер, связанный с событиями базы данных, может вызываться при следующих событиях:

При соединении с базой данных (CONNECT).
При отсоединении от базы данных (DISCONNECT).
При старте транзакции (TRANSACTION START).
При подтверждении транзакции (TRANSACTION COMMIT).
При отмене транзакции (TRANSACTION ROLLBACK).

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

Триггеры на событие TRANSACTION срабатывают при старте транзакции, её подтверждении или отмене. Не перехваченные исключения обрабатываются в зависимости от типа события TRANSACTION:

для события START исключение возвращается клиенту, а транзакция отменяется;
для события COMMIT исключение возвращается клиенту, действия, выполненные триггером, и транзакция отменяются;
для события ROLLBACK исключение не возвращается клиенту, а транзакция, как и предусмотрено, отменяется.

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

CREATE TRIGGER TR_LOG_CONNECT ON CONNECT
AS
BEGIN
   INSERT INTO LOG_CONNECT (ID, USERNAME, ATIME)
      VALUES (NEXT VALUE FOR SEQ_LOG_CONNECT, CURRENT_USER, CURRENT_TIMESTAMP);
END

Следующий пример показывает как с помощью триггера на подключение, контролировать доступ пользователей в БД:

CREATE EXCEPTION E_INCORRECT_WORKTIME 'Не рабочее время.';

CREATE TRIGGER TR_LIMIT_WORKTIME ACTIVE ON CONNECT
AS
BEGIN
  IF ((CURRENT_USER <> 'SYSDBA') AND
      NOT (CURRENT_TIME BETWEEN time '9:00' AND time '17:00')) THEN
    EXCEPTION E_INCORRECT_WORKTIME;
END

DDL триггеры

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

CREATE TRIGGER <имя триггера> {
         [ACTIVE | INACTIVE]
         {BEFORE | AFTER} 
         {ANY DDL STATEMENT | <DDL событие> [OR <DDL событие> ...] }
         [POSITION <порядок срабатывания триггера>]
AS
   [<объявление> [<объявление> ...] ]
BEGIN
   <блок операторов>
END

При этом возможные события задаются следующим образом.

<DDL событие> ::=
           CREATE|ALTER|DROP TABLE
         | CREATE|ALTER|DROP PROCEDURE
         | CREATE|ALTER|DROP FUNCTION
         | CREATE|ALTER|DROP TRIGGER
         | CREATE|ALTER|DROP EXCEPTION
         | CREATE|ALTER|DROP VIEW
         | CREATE|ALTER|DROP DOMAIN
         | CREATE|ALTER|DROP ROLE
         | CREATE|ALTER|DROP SEQUENCE
         | CREATE|ALTER|DROP USER
         | CREATE|ALTER|DROP INDEX
         | CREATE|DROP COLLATION
         | ALTER CHARACTER SET
         | CREATE|ALTER|DROP PACKAGE
         | CREATE|DROP PACKAGE BODY
         | CREATE|ALTER|DROP MAPPING

Если в качестве события указано предложение ANY DDL STATEMENT, то триггер будет вызван при наступлении любого из DDL событий.

DDL триггеры срабатывают на указанные события изменения метаданных в одной из фаз события. BEFORE триггеры запускаются до изменений в системных таблицах, AFTER триггеры запускаются после изменений в системных таблицах.

Когда оператор DDL запускает триггер, в котором выбрасывается исключение, оператор не будет фиксирован. Т.е. исключения могут использоваться, чтобы гарантировать, что оператор DDL будет отменен, если некоторые условия не будут соблюдены.

Действия DDL триггеров выполняются только при фиксации транзакции, в которой работает затронутая DDL команда.

Чтобы узнать, какое именно событие произошло, объект, над которым производится действие и другую информацию, можно использовать встроенную функцию RDB$GET_CONTEXT.

Для этого в ней доступно пространство имён DDL_TRIGGER.

Контекст DDL_TRIGGER работает как стек. Перед выполнением DDL триггера, значения, относящиеся к выполняемой команде, помещаются в этот стек. После завершения работы триггера значения выталкиваются. Таким образом, в случае каскадных DDL операторов, когда каждая пользовательская DDL команда возбуждает DDL триггер, и этот триггер запускает другие DDL команды, значения переменных в пространстве имен DDL_TRIGGER будут соответствовать команде, которая вызвала последний DDL триггер в стеке вызовов.

Переменные доступные в пространстве имён DDL_TRIGGER:

EVENT_TYPE — тип события (CREATE, ALTER, DROP);
OBJECT_TYPE — тип объекта (TABLE, VIEW и д.р.);
DDL_EVENT — имя события (EVENT_TYPE || ' ' || OBJECT_TYPE);
OBJECT_NAME — имя объекта метаданных;
SQL_TEXT — текст SQL запроса.

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

CREATE EXCEPTION E_INVALID_SP_NAME 'Имя хранимой процедуры должно начинаться с "SP_")';

CREATE TRIGGER TRIG_DDL_SP BEFORE CREATE PROCEDURE
AS
BEGIN
  IF (RDB$GET_CONTEXT('DDL_TRIGGER', 'OBJECT_NAME') NOT STARTING 'SP_') THEN
    EXCEPTION E_INVALID_SP_NAME;
END;

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

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

Изменение триггера

Для изменения заголовка и/или тела существующего триггера используется оператор ALTER TRIGGER.

ALTER TRIGGER <имя триггера>
   [ACTIVE | INACTIVE]
   [{BEFORE | AFTER} <список событий>]
   [POSITION <порядок срабатывания триггера>]
[AS
   [<объявление> [<объявление> ...] ]
BEGIN
   <блок операторов>
END]

В операторе изменения триггера можно изменить его состояние активности (ACTIVE / INACTIVE), событие (события) таблицы (представления) и фазу события, позицию триггера, выполняемые триггером действия. Если триггер был создан для события базы данных или для события изменения метаданных, то можно только изменить его активность, позицию и выполняемые действия. Если какой-то элемент не указан, то его первоначальное значение не изменяется.

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

Удаление триггера

Для удаления существующего триггера используется оператор DROP TRIGGER.

DROP TRIGGER <имя триггера>;

Нельзя удалить триггер, автоматически созданный системой для поддержания ограничений PRIMARY KEY, CHECK и FOREIGN KEY. Остальные триггеры не имеют никаких зависимостей, которые ограничили бы возможности удаления триггеров.

Контрольные вопросы

Как можно вызвать триггер?
Для чего используются триггеры?
Какие виды триггеров бывают?
Что такое “фаза выполнения”?
В каких случаях допустимо изменение столбцов записей?
На какие события в БД допустимо создавать триггеры?
Как узнать какой объект вызвал DDL триггер?
В каком порядке срабатывают триггеры?
Какие есть ограничения при удалении триггера?