21. Триггеры (TRIGGER)

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

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

• Табличные или DML триггеры;

• Триггеры на события базы данных;

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

Порядок выполнения

Для каждой комбинации фазы и события может быть определено несколько триггера. Порядок их выполнения может быть указан в явном виде с помощью необязательного предложения POSITION в определении триггера. Всего доступно 32 767 позиций. Триггеры с наименьшими значениями позиций запускаются первыми. Если аргумент POSITION не указан, то по умолчанию позиция равна 0. Если несколько триггеров имеют одинаковую позицию и фазу, то они будут выполняться в неопределенном порядке, при этом соблюдается общий порядок по позиции и фазе.

DML триггеры

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

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

• до добавления новой строки (BEFORE INSERT);

• после добавления новой строки (AFTER INSERT);

• до изменения строки (BEFORE UPDATE);

• после изменения строки (AFTER UPDATE);

• до удаления строки (BEFORE DELETE);

• после удаления строки (AFTER DELETE).

Эти базовые формы предназначены для создания триггеров с одной фазой и одним событием. Также поддерживаются формы для создания триггеров для одной фазы и нескольких событий, например, BEFORE INSERT OR UPDATE OR DELETE или AFTER UPDATE OR DELETE. Многофазные триггеры, такие как BEFORE OR AFTER, невозможны. Логические контекстные переменные INSERTING, UPDATING и DELETING можно использовать в теле триггера для определения типа события, вызвавшего срабатывание триггера.

Контекстные переменные OLD и NEW

Для триггеров DML существуют контекстные переменные OLD и NEW. Каждая из них содержит запись значений всей строки: одна для значений до события изменения данных (фаза BEFORE), другая - для значений, которые будут после события (фаза AFTER). В операторах на эти контекстные переменные ссылаются, используя форму NEW.<имя столбца> и OLD.<имя столбца> соответственно. В триггерах можно обращаться к значению любого столбца таблицы (представления) до его изменения в клиентской программе (для этого перед именем столбца помещается ключевое слово OLD и точка) и после изменения (перед именем столбца помещается NEW и точка).

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

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

Для BEFORE UPDATE и BEFORE INSERT переменная NEW доступна для чтения и записи, только если не затрагивает вычисляемый столбец COMPUTED BY.

Триггеры на события базы данных

События базы данных, для которых может быть определён триггер:

Таблица 21.1 Триггеры на события базы данных

Событие описание	Описание
CONNECT	Перед выполнением триггера автоматически запускается транзакция с уровнем изоляции по умолчанию.
DISCONNECT	Перед выполнением триггера автоматически запускается транзакция с уровнем изоляции по умолчанию.
TRANSACTION START	Триггер выполняется в контексте текущей транзакции.
TRANSACTION COMMIT	Триггер выполняется в контексте транзакции, выполняющей фиксацию.
TRANSACTION ROLLBACK	Триггер выполняется в контексте транзакции, выполняющей откат.

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

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

• для события TRANSACTION START исключение возвращается клиенту, а транзакция отменяется;

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

• для события TRANSACTION ROLLBACK исключение не возвращается клиенту, а транзакция отменяется.

Из вышеизложенного следует, что нет прямого способа узнать, какой триггер (DISCONNECT или ROLLBACK) вызвал исключение. Также ясно, что вы не сможете подключиться к базе данных в случае исключения в триггере на событие CONNECT, а также отменяется старт транзакции при исключении в триггере на событие TRANSACTION START. В обоих случаях база данных блокируется до тех пор, пока вы не отключите триггеры базы данных и не исправите ошибочный код.

В некоторые утилиты командной строки были добавлены новые ключи для отключения триггеров на базу данных:

gbak -nodbtriggers
isql -nodbtriggers
nbackup -T

В случае двухфазных транзакций триггеры на событие TRANSACTION START срабатывают в фазе подготовки (prepare), а не в фазе commit.

Использование оператора IN AUTONOMOUS TRANSACTION DO в триггерах событий базы данных, связанных с транзакциями (TRANSACTION START, TRANSACTION ROLLBACK, TRANSACTION COMMIT), может привести к тому, что автономная транзакция попадет в бесконечный цикл.

Триггеры событий DISCONNECT и TRANSACTION ROLLBACK не будут выполняться при отключении клиентов через таблицы мониторинга (DELETE FROM MON$ATTACHMENTS).

DDL триггеры

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

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

Предупреждение

Тип события [BEFORE | AFTER] для триггера DDL изменить нельзя.

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

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

Для операторов CREATE OR ALTER... триггер срабатывает один раз для события CREATE или события ALTER, в зависимости от того существовал ли ранее объект. Для операторов RECREATE триггер вызывается для события DROP, если объект существовал, и после этого для события CREATE.

Если объект метаданных не существует, то обычно триггеры на события ALTER и DROP не запускаются. Исключением из правила являются BEFORE ALTER/DROP USER триггеры, которые будут вызваны, даже если имя пользователя не существует. Это вызвано тем, что эти команды выполняются для базы данных безопасности, для которой не делается проверка существования пользователей перед их выполнением. Данное поведение, вероятно, будет отличаться для встроенных пользователей.

Если некоторое исключение возбуждено после того как начала выполняться DDL команда и до того как запущен AFTER триггер, то AFTER триггер не запускается.

Для процедур и функций в составе пакетов не запускаются индивидуальные триггеры {CREATE | ALTER | DROP} {PROCEDURE | FUNCTION}.

Оператор ALTER DOMAIN <старое имя> TO <новое имя> устанавливает контекстные переменные OLD_OBJECT_NAME и NEW_OBJECT_NAME в обоих триггерах BEFORE и AFTER. Контекстная переменная OBJECT_NAME будет содержать старое имя объекта метаданных в триггере BEFORE, и новое — в триггере AFTER.

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

Пространство имён DDL_TRIGGER

Во время работы DDL триггера доступно пространство имён DDL_TRIGGER для использования в функции RDB$GET_CONTEXT. Его использование также допустимо в хранимых процедурах и функциях, вызванных DDL триггерами.

Контекст DDL_TRIGGER работает как стек. Перед возбуждением DDL триггера, значения, относящиеся к выполняемой команде, помещаются в этот стек. После завершения работы триггера значения выталкиваются. Таким образом, в случае каскадных DDL операторов, когда каждая пользовательская DDL команда возбуждает DDL триггер, и этот триггер запускает другие DDL команды, с помощью EXECUTE STATEMENT, значения переменных в пространстве имен 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-запроса.

21.1. Создание триггера

Для создания триггера используется оператор CREATE TRIGGER, синтаксис которого представлен в листинге .

Листинг 21.1 Синтаксис оператора создания триггера CREATE TRIGGER

CREATE TRIGGER <имя триггера> {
      <объявление табличного триггера>
    | <объявление табличного триггера в стандарте SQL-2003>
    | <объявление триггера базы данных>
    | <объявление DDL триггера> }
[SQL SECURITY {DEFINER | INVOKER}]
{ EXTERNAL NAME '<внешний модуль>' ENGINE <имя движка> [AS <тело внеш. триг.>]} |
{
   AS
      [<объявление> [<объявление> ...] ]
   BEGIN
      <блок операторов>
   END }

<объявление табличного триггера> ::=
         FOR {<имя таблицы> | <имя представления>}
         [ACTIVE | INACTIVE]
         {BEFORE | AFTER} <список событий таблицы (представления)>
         [POSITION <порядок срабатывания триггера>]

<объявление табличного триггера в стандарте SQL-2003> ::=
         [ACTIVE | INACTIVE]
         {BEFORE | AFTER} <список событий таблицы (представления)>
         ON {<имя таблицы> | <имя представления>}
         [POSITION <порядок срабатывания триггера>]

<объявление триггера базы данных> ::=
         [ACTIVE | INACTIVE]
         ON <событие соединения или транзакции>
         [POSITION <порядок срабатывания триггера>]

<объявление DDL триггера> ::=
         [ACTIVE | INACTIVE]
         {BEFORE | AFTER} <список DDL событий>
         [POSITION <порядок срабатывания триггера>]

<список событий таблицы (представления)> ::= <событие DML> [OR <событие DML>...]

<событие DML> ::= { INSERT | UPDATE | DELETE }

<событие соединения или транзакции> ::= {
           CONNECT
         | DISCONNECT
         | TRANSACTION START
         | TRANSACTION COMMIT
         | TRANSACTION ROLLBACK }

<список DDL событий> ::= {
           <любой оператор DDl>
         | <DDL событие> [OR <DDL событие> ...] }

<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

<объявление> ::= <объявление локальной переменной>;
               | <объявление курсора>;
               | <объявление процедуры/функции>
               | <реализация процедуры/функции>

Оператор CREATE TRIGGER используется для создания нового триггера. Триггер может быть создан либо для события таблицы/представления (или комбинации событий), для события базы данных и для события DDL.

Оператор CREATE TRIGGER является составным оператором, содержащими заголовок и тело. Заголовок определяет имя триггера, а также содержит имя таблицы/представления, фазу триггера, событие (или события) на которые срабатывает триггер и позицию. Тело триггера состоит из необязательных объявлений локальных переменных, подпрограмм и именованных курсоров, и одного или нескольких операторов или блоков операторов, заключённых во внешнем блоке, который начинается с ключевого слова BEGIN и заканчивается ключевым словом END. Объявления и внутренние операторы завершаются точкой с запятой (;).

Имя триггера может содержать до 63 символов и должно быть уникальным среди имен всех триггеров базы данных.

Состояние триггера

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

Внешние триггеры

Триггер может быть расположена во внешнем модуле. В этом случае вместо тела триггера указывается место его расположения во внешнем модуле с помощью предложения EXTERNAL NAME. Аргументом этого предложения является строка, в которой через разделитель указано имя внешнего модуля, имя процедуры внутри модуля и определённая пользователем информация. В предложении ENGINE указывается имя движка для обработки подключения внешних модулей. В РЕД Базе Данных для работы с внешними модулями используется движок UDR. После ключевого слова AS может быть указан строковый литерал — "тело" внешнего триггера, оно может быть использовано внешнем модулем для различных целей.

Привилегии выполнения

Необязательное предложение SQL SECURITY {DEFINER | INVOKER} определяет, в контексте какого пользователя будет выполняться триггер. Ключевое слово INVOKER (значение по умолчанию) указывает, что триггер выполняется с правами вызвавшего его пользователя. Задание ключевого слова DEFINER означает, что триггер выполняется с правами к объектам базы данных его владельца (создателя). Значение по умолчанию на уровне всей базы данных можно изменить оператором ALTER DATABASE SET DEFAULT SQL SECURITY.

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

DML триггеры

Табличный триггер может быть создан владельцем таблицы (представления), для которой создается DML триггер, администратором и пользователем с привилегией ALTER ANY {TABLE|VIEW}.

Фаза

Фаза определяет время срабатывания триггера по отношению к операции изменения данных:

• Триггер BEFORE срабатывает перед операцией изменения данных (вставка, обновление или удаление).

• Триггер AFTER запускается после завершения операции изменения данных.

Порядок срабатывания

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

События строк

В определении триггера таблицы/представления указывается хотя бы одна из DML-операций (INSERT, UPDATE и DELETE), чтобы указать одно или несколько событий, на которые должен сработать триггер. Если указано несколько операций, они должны быть разделены ключевым словом OR. Ни одна операция не может выполняться более одного раза. Внутри блока операторов можно использовать логические контекстные переменные INSERTING, UPDATING и DELETING для проверки того, какая операция выполняется в данный момент.

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

Триггеры на события базы данных и на события изменения метаданных может создавать только владелец базы данных, администратор и пользователь с привилегией ALTER DATABASE.

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

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

Листинг 21.2 Синтаксис оператора изменения триггера ALTER TRIGGER

ALTER TRIGGER <имя триггера>
[ACTIVE | INACTIVE]
[{BEFORE | AFTER} <список событий таблицы (представления)>]
[POSITION <порядок срабатывания триггера>]
[SQL SECURITY {DEFINER | INVOKER} | DROP SQL SECURITY]
[{EXTERNAL NAME '<внешний модуль>' ENGINE <имя движка> [AS <тело внеш. триг.>]} |
{
   AS
      [<объявление> [<объявление> ...] ]
   BEGIN
      <блок операторов>
   END }]

<список событий таблицы (представления)> ::= <событие DML> [OR <событие DML>...]

<событие DML> ::= { INSERT | UPDATE | DELETE }

Примечание

DML триггер может быть изменен администратором и владельцем таблицы или представления или пользователем с привилегией ALTER ANY {TABLE | VIEW}.

Триггеры для событий базы данных и триггеры событий на изменение метаданных может изменить администратор, владелец базы данных или пользователь с привилегией ALTER DATABASE.

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

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

Триггер DML нельзя изменить на триггер базы данных или триггер DDL.

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

21.3. Создание нового или изменение существующего триггера

Оператор CREATE OR ALTER TRIGGER создает новый триггер для таблицы или представления, если триггера с таким именем не существует в базе данных. Иначе изменяет и перекомпилирует его, при этом существующие права и зависимости сохраняются.

Листинг 21.3 Синтаксис оператора создания нового или изменения существующего триггера CREATE OR ALTER TRIGGER

CREATE OR ALTER TRIGGER <имя триггера> {
   <объявление табличного триггера>
 | <объявление табличного триггера в стандарте SQL-2003>
 | <объявление триггера базы данных>
 | <объявление DDL триггера> }
[SQL SECURITY {DEFINER | INVOKER}]
{ EXTERNAL NAME '<внешний модуль>' ENGINE <имя движка> [AS <тело внеш. триг.>] } |
{
   AS
      [<объявление> [<объявление> ...] ]
   BEGIN
      <блок операторов>
   END }

Синтаксис этого оператора соответствует синтаксису оператора CREATE TRIGGER.

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

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

Листинг 21.4 Синтаксис оператора удаления триггера DROP TRIGGER

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

Примечание

DML триггер может быть удален администратором и владельцем таблицы или представления или пользователем с привилегией ALTER ANY {TABLE | VIEW}.

Триггеры для событий базы данных и триггеры событий на изменение метаданных может удалить администратор, владелец базы данных или пользователь с привилегией ALTER DATABASE.

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

21.5. Создание нового или пересоздание существующего триггера

Оператор RECREATE TRIGGER создаёт новый триггер, если триггер с указанным именем не существует, в противном случае оператор RECREATE TRIGGER попытается удалить его и создать новый.

Листинг 21.5 Синтаксис оператора создания нового или пересоздания существующего триггера RECREATE TRIGGER

RECREATE TRIGGER <имя триггера> {
   <объявление табличного триггера>
 | <объявление табличного триггера в стандарте SQL-2003>
 | <объявление триггера базы данных>
 | <объявление DDL триггера> }
[SQL SECURITY {DEFINER | INVOKER}]
{ EXTERNAL NAME '<внешний модуль>' ENGINE <имя движка> [AS <тело внеш. триг.>] } |
{
   AS
      [<объявление> [<объявление> ...] ]
   BEGIN
      <блок операторов>
   END }

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

Синтаксис этого оператора соответствует синтаксису оператора CREATE TRIGGER.

21.6. Примеры триггеров

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

Формирование значения искусственного первичного ключа

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

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

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

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

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

Вместо конструкции NEXT VALUE FOR можно использовать и встроенную функцию GEN_ID:

NEW.COD = GEN_ID(GEN_PEOPLE, 1);

Передача сообщений клиентским процессам об изменении данных

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

SET TERM ^;
CREATE TRIGGER TAC_COUNTRY
   FOR COUNTRY
   AFTER INSERT OR UPDATE OR DELETE
AS BEGIN
   POST_EVENT 'COUNTRY_CHANGED';
END ^

Триггер создается для таблицы COUNTRY для фазы после (AFTER) событий добавления, изменения и удаления (INSERT, UPDATE, DELETE). Если транзакция, в контексте которой выполнялись соответствующие операторы, будет подтверждена (будет выполнен оператор COMMIT или COMMIT RETAINING), то все клиентские приложения, которые прослушивают это сообщение, получат соответствующий сигнал. При отмене такой транзакции (оператор ROLLBACK) сообщение клиентам передано не будет.

Как правило, реакцией клиентов на подобное сообщение является как минимум переоткрытие соответствующего набора данных, а в некоторых случаях и перезапуск транзакции (транзакций с уровнями изоляции SNAPSHOT и SNAPSHOT TABLE STABILITY).

Пример триггера, обеспечивающего поддержание ссылочной целостности данных

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

Следующий триггер TAU_COUNTRY выполняет все необходимые действия по поддержанию соответствия внешних ключей подчиненной таблицы регионов (REGION) первичному ключу таблицы стран (COUNTRY) при изменении значения первичного ключа (код страны) в справочнике стран.

SET TERM ^;
RECREATE TRIGGER TAU_COUNTRY
   FOR COUNTRY
   AFTER UPDATE
AS BEGIN
   IF (OLD.CODCOUNTRY <> NEW.CODCOUNTRY) THEN
      UPDATE REGION
         SET REGION.CODCOUNTRY = NEW.CODCOUNTRY
         WHERE REGION.CODCOUNTRY = OLD.CODCOUNTRY;
END ^

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

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

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

Пример триггера, создающего запись истории окладов

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

CREATE TABLE EMPLOYEE (
   emp_no SMALLINT NOT NULL PRIMARY KEY,
   first_name VARCHAR(15) NOT NULL,
   last_name VARCHAR(20) NOT NULL,
   phone_ext VARCHAR(4),
   hire_date DATE DEFAULT 'NOW' NOT NULL,
   dept_no CHAR(3),
   job_code VARCHAR(5) NOT NULL,
   job_grade SMALLINT NOT NULL,
   job_country VARCHAR(15) NOT NULL,
   salary NUMERIC(10,2) NOT NULL,
   full_name COMPUTED BY (last_name || ', ' || first_name));

CREATE TABLE SALARY_HISTORY (
   emp_no SMALLINT NOT NULL,
   change_date DATE DEFAULT 'NOW' NOT NULL,
   updater_id VARCHAR(20) NOT NULL,
   old_salary NUMERIC(10,2) NOT NULL,
   percent_change DOUBLE PRECISION
      DEFAULT 0
      NOT NULL
      CHECK (percent_change between -50 and 50),
   new_salary COMPUTED BY (old_salary + old_salary * percent_change / 100),
   PRIMARY KEY (emp_no, change_date, updater_id),
   FOREIGN KEY (emp_no) REFERENCES employee (emp_no)  );

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

CREATE TRIGGER SAVE_SALARY_CHANGE
   FOR EMPLOYEE
   AFTER UPDATE
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 ^

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

Одно из назначений триггеров — преобразование неизменяемых представлений в изменяемые. Примеры таких триггеров см. в главе 16.