Руководство По Языку SQL СУБД Firebird 5.0 | CREATE TRIGGER | Триггеры На События Изменения Метаданных

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

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

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

Особенности:

BEFORE триггеры запускаются до изменений в системных таблицах. AFTER триггеры запускаются после изменений в системных таблицах.
Когда оператор DDL запускает триггер, в котором возбуждается исключение (BEFORE или AFTER, преднамеренно или неумышленно), оператор не будет фиксирован. Т.е. исключения могут использоваться, чтобы гарантировать, что оператор DDL будет отменен, если некоторые условия не будут соблюдены.
Действия DDL триггеров выполняются только при фиксации транзакции, в которой работает затронутая DDL команда. Никогда не забывайте о том, что в AFTER триггере, возможно сделать только то, что возможно сделать после DDL команды без автоматической фиксации транзакций. Вы не можете, например, создать таблицу в триггере и использовать её там.
Для операторов CREATE OR ALTER … триггер срабатывает один раз для события CREATE или события ALTER, в зависимости от того существовал ли ранее объект. Для операторов RECREATE триггер вызывается для события DROP, если объект существовал, и после этого для события CREATE.
Если объект метаданных не существует, то обычно триггеры на события ALTER и DROP не запускаются. Исключения описаны в пункте 6.
Исключением из правила 5 являются BEFORE {ALTER | DROP} USER триггеры, которые будут вызваны, даже если имя пользователя не существует. Это вызвано тем, что эти команды выполняются для базы данных безопасности, для которой не делается проверка существования пользователей перед их выполнением. Данное поведение, вероятно, будет отличаться для встроенных пользователей, поэтому не пишите код, который зависит от этого.
Если некоторое исключение возбуждено после того как начала выполняться DDL команда и до того как запущен AFTER триггер, то AFTER триггер не запускается.
Для процедур и функций в составе пакетов не запускаются индивидуальные триггеры {CREATE | ALTER | DROP} {PROCEDURE | FUNCTION}.
Оператор ALTER DOMAIN old name TO new name устанавливает контекстные переменные 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 – имя события (<ddl event item>),

где <ddl event item> = EVENT_TYPE || ' ' || OBJECT_TYPE
OBJECT_NAME – имя объекта метаданных
OLD_OBJECT_NAME – имя объекта метаданных до переименования
NEW_OBJECT_NAME – имя объекта метаданных после переименования
SQL_TEXT – текст SQL запроса

Отключение триггеров

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

gbak -nodbtriggers
isql -nodbtriggers
nbackup -T

Эти ключи могут использоваться только SYSDBA или владельцем базы данных.

Кто может создать триггеры на события изменения метаданных?

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

Администраторы
Владелец базы данных;
Пользователи с привилегией ALTER DATABASE.

Примеры

Example 1. Контроль наименования объектов базы данных с помощью DDL триггера

CREATE EXCEPTION e_invalid_sp_name
  'Неверное имя хранимой процедуры (должно начинаться с SP_)';

SET TERM !;

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!

-- Test
CREATE PROCEDURE sp_test
AS
BEGIN
END!

CREATE PROCEDURE test
AS
BEGIN
END!

-- Statement failed, SQLSTATE = 42000
-- exception 1
-- -E_INVALID_SP_NAME
-- -Неверное имя хранимой процедуры (должно начинаться с SP_)
-- -At trigger 'TRIG_DDL_SP' line: 4, col: 5

SET TERM ;!

Example 2. Контроль безопасности DDL операторов

CREATE EXCEPTION e_access_denied 'Access denied';

SET TERM !;

CREATE TRIGGER trig_ddl BEFORE ANY DDL STATEMENT
AS
BEGIN
  IF (current_user <> 'SUPER_USER') THEN
    EXCEPTION e_access_denied;
END!

-- Test
CREATE PROCEDURE sp_test
AS
BEGIN
END!

-- The last command raises this exception and procedure SP_TEST is not created
-- Statement failed, SQLSTATE = 42000
-- exception 1
-- -E_ACCESS_DENIED
-- -Access denied
-- -At trigger 'TRIG_DDL' line: 4, col: 5

SET TERM ;!

Note	В Firebird существуют привилегии на DDL операторы, поэтому прибегать к написанию DDL триггера нужно только в случае, если того же самого эффекта невозможно достичь стандартными методами.

Example 3. Использование DDL триггеров для регистрации событий изменения метаданных

CREATE SEQUENCE ddl_seq;

CREATE TABLE ddl_log (
  id BIGINT NOT NULL PRIMARY KEY,
  moment TIMESTAMP NOT NULL,
  user_name VARCHAR(63) NOT NULL,
  event_type VARCHAR(25) NOT NULL,
  object_type VARCHAR(25) NOT NULL,
  ddl_event VARCHAR(25) NOT NULL,
  object_name VARCHAR(63) NOT NULL,
  old_object_name VARCHAR(63),
  new_object_name VARCHAR(63),
  sql_text BLOB sub_type text NOT NULL,
  ok CHAR(1) NOT NULL
);

SET TERM !;

CREATE TRIGGER trig_ddl_log_before BEFORE ANY DDL STATEMENT
AS
  DECLARE id TYPE OF COLUMN ddl_log.id;
BEGIN
  -- Мы должны производить изменения в AUTONOMOUS TRANSACTION,
  -- таким образом, если произойдёт исключение и команда
  -- не будет запущена, она всё равно будет зарегистрирована.
  IN AUTONOMOUS TRANSACTION DO
  BEGIN
    INSERT INTO ddl_log (
      id, moment, user_name, event_type, object_type, ddl_event,
      object_name, old_object_name, new_object_name, sql_text, ok)
    VALUES (NEXT VALUE FOR ddl_seq,
            current_timestamp, current_user,
            rdb$get_context('DDL_TRIGGER', 'EVENT_TYPE'),
            rdb$get_context('DDL_TRIGGER', 'OBJECT_TYPE'),
            rdb$get_context('DDL_TRIGGER', 'DDL_EVENT'),
            rdb$get_context('DDL_TRIGGER', 'OBJECT_NAME'),
            rdb$get_context('DDL_TRIGGER', 'OLD_OBJECT_NAME'),
            rdb$get_context('DDL_TRIGGER', 'NEW_OBJECT_NAME'),
            rdb$get_context('DDL_TRIGGER', 'SQL_TEXT'),
            'N')
    RETURNING id INTO id;
    rdb$set_context('USER_SESSION', 'trig_ddl_log_id', id);
  END
END!

-- Примечание:
-- созданный выше триггер будет запущен для этой DDL.
-- Хорошей идеей является использование –nodbtriggers
-- при работе с ним
CREATE TRIGGER trig_ddl_log_after AFTER ANY DDL STATEMENT
AS
BEGIN
  -- Здесь нам требуется автономная транзакция,
  -- потому что в оригинальной транзакции
  -- мы не увидим запись, вставленную в
  -- BEFORE триггере в автономной транзакции,
  -- если пользовательская транзакции не запущена
  -- с режимом изоляции  READ COMMITTED.
  IN AUTONOMOUS TRANSACTION DO
    UPDATE ddl_log SET ok = 'Y'
    WHERE
      id = rdb$get_context('USER_SESSION', 'trig_ddl_log_id');
END!

COMMIT!

SET TERM ;!

-- Удаляем запись о создании trig_ddl_log_after.
DELETE FROM ddl_log;
COMMIT;

-- Тест

-- Эта команда будет зарегистрирована единожды
-- (т.к. T1 не существует, RECREATE вызовет событие CREATE)
-- с OK = Y.
RECREATE TABLE t1 (
  n1 INTEGER,
  n2 INTEGER
);

-- Оператор не выполнится, т.к. T1 уже существует,
-- таким образом OK будет иметь значение N.
CREATE TABLE t1 (
  n1 INTEGER,
  n2 INTEGER
);

-- T2 не существует. Это действие не будет зарегистрировано.
DROP TABLE t2;

-- Это действие будет зарегистрировано дважды
-- (т.к. T1 существует, действие RECREATE рассматривается
-- как DROP и CREATE) с полем OK = Y.
RECREATE TABLE t1 (
  n INTEGER
);

CREATE DOMAIN dom1 AS INTEGER;

ALTER DOMAIN dom1 TYPE BIGINT;

ALTER DOMAIN dom1 TO dom2;

COMMIT;

SELECT
  id,
  ddl_event,
  object_name as name,
  sql_text,
  ok
FROM ddl_log
ORDER BY id;

 ID DDL_EVENT                 OBJECT_NAME                      SQL_TEXT OK
=== ========================= ======================= ================= ======
  2 CREATE TABLE              T1                                   80:3 Y
====================================================
SQL_TEXT:
recreate table t1 (
    n1 integer,
    n2 integer
)
====================================================
  3 CREATE TABLE              T1                                   80:2 N
====================================================
SQL_TEXT:
create table t1 (
    n1 integer,
    n2 integer
)
====================================================
  4 DROP TABLE                T1                                   80:6 Y
====================================================
SQL_TEXT:
recreate table t1 (
    n integer
)
====================================================
  5 CREATE TABLE              T1                                   80:9 Y
====================================================
SQL_TEXT:
recreate table t1 (
    n integer
)
====================================================

См. также:

ALTER TRIGGER, DROP TRIGGER.

`ALTER TRIGGER`

Назначение

Изменение существующего триггера.

Доступно в

DSQL, ESQL

Синтаксис

ALTER TRIGGER trigname
[ACTIVE | INACTIVE]
[{BEFORE | AFTER} <mutation_list>]
[POSITION number]
[SQL SECURITY {DEFINER | INVOKER} | DROP SQL SECURITY]
[<routine-body>]

<mutation_list> ::= <mutation> [OR <mutation> [OR <mutation>]]

<mutation> ::= { INSERT | UPDATE | DELETE }

Полное описание оператора см. CREATE TRIGGER.

Допустимые изменения

В операторе изменения триггера можно изменить:

Состояние активности (ACTIVE | INACTIVE);
Фазу (BEFORE | AFTER);
Событие(я);
Позицию срабатывания;
Привилегии выполнения триггера: вызывающего пользователя (SQL SECURITY INVOKER), определяющего пользователя (SQL SECURITY DEFINER) или наследует у таблицы (DROP SQL SECURITY);
Код тела триггера.

Если какой-либо элемент не указан, то он остаётся без изменений.

Note	DML триггер невозможно изменить в триггер на событие базы данных и наоборот. Событие в триггере базы данных невозможно изменить.

Tip

Помните

Триггер с ключевым словом BEFORE наступает до соответствующего события, с ключевым словом AFTER — после соответствующего события.

Один DML триггер может содержать более одного события (INSERT, UPDATE, DELETE). События должны быть разделены ключевым словом OR.Каждое из событий может быть указано не более одного раза.

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

Кто может изменить триггеры?

DML триггеры могут изменить:

Администраторы
Владелец таблицы (представления);
Пользователи с привилегией ALTER ANY {TABLE | VIEW}.

Триггеры для событий базы данных и триггеры событий на изменение метаданных могут изменить:

Администраторы
Владелец базы данных;
Пользователь, имеющий привилегию ALTER DATABASE.

Примеры

Example 1. Отключение (перевод в неактивное состояние) триггера

ALTER TRIGGER set_cust_no INACTIVE;

Example 2. Изменение позиции триггера

ALTER TRIGGER set_cust_no POSITION 14;

Example 3. Перевод триггера в неактивное состояние и изменение списка событий

ALTER TRIGGER TR_CUST_LOG
INACTIVE AFTER INSERT OR UPDATE;

Example 4. Изменение привилегий выполнения триггера

После выполнения данного оператора триггер будет выполняться с привилегиями определяющего пользователя (владельца).

ALTER TRIGGER TR_CUST_LOG
SQL SECURITY DEFINER;

Example 5. Удаление привилегий выполнения триггера

После удаления привилегий выполнения триггера, триггер выполняется с привилегиями унаследованными от таблицы.Если у таблицы не определены привилегии выполнения, то триггер будет выполняться с привилегиями вызывающего пользователя.

ALTER TRIGGER TR_CUST_LOG
DROP SQL SECURITY;

Example 6. Перевод триггера в активное состояние, изменение его позиции и его тела

ALTER TRIGGER tr_log_connect
ACTIVE POSITION 1
AS
BEGIN
  INSERT INTO LOG_CONNECT (ID,
                           USERNAME,
                           ROLENAME,
                           ATIME)
  VALUES (NEXT VALUE FOR SEQ_LOG_CONNECT,
          CURRENT_USER,
          CURRENT_ROLE,
          CURRENT_TIMESTAMP);
END

См. также:

CREATE TRIGGER,CREATE OR ALTER TRIGGER,RECREATE TRIGGER.

`CREATE OR ALTER TRIGGER`

Назначение

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

Доступно в

DSQL, ESQL

Синтаксис

CREATE OR ALTER TRIGGER trigname {
    <relation_trigger_legacy>
  | <relation_trigger_sql2003>
  | <database_trigger>
  | <ddl_trigger> }
<routine-body>

Полное описание оператора см. CREATE TRIGGER.

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

Примеры

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

CREATE OR ALTER TRIGGER set_cust_no
ACTIVE BEFORE INSERT ON customer POSITION 0
AS
BEGIN
  IF (NEW.cust_no IS NULL) THEN
    NEW.cust_no = GEN_ID(cust_no_gen, 1);
END

См. также:

CREATE TRIGGER, ALTER TRIGGER, RECREATE TRIGGER.

`DROP TRIGGER`

Назначение

Удаление существующего триггера.

Доступно в

DSQL, ESQL

Синтаксис

DROP TRIGGER trigname

Table 1. Параметры оператора `DROP TRIGGER`
Параметр	Описание
trigname	Имя триггера.

Оператор DROP TRIGGER удаляет существующий триггер.

Кто может удалить триггеры?

DML триггеры могут удалить:

Администраторы
Владелец таблицы (представления);
Пользователи с привилегией ALTER ANY {TABLE | VIEW}.

Триггеры для событий базы данных и триггеры событий на изменение метаданных могут удалить:

Администраторы
Владелец базы данных;
Пользователь, имеющий привилегию ALTER DATABASE.

Примеры

Example 1. Удаление триггера

DROP TRIGGER set_cust_no;

См. также:

CREATE TRIGGER, ALTER TRIGGER.

`RECREATE TRIGGER`

Назначение

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

Доступно в

DSQL, ESQL

Синтаксис

RECREATE TRIGGER trigname {
    <relation_trigger_legacy>
  | <relation_trigger_sql2003>
  | <database_trigger>
  | <ddl_trigger> }
<routine-body>

Полное описание оператора см. CREATE TRIGGER.

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

Примеры

Example 1. Создание или пересоздание триггера

RECREATE TRIGGER set_cust_no
ACTIVE BEFORE INSERT ON customer POSITION 0
AS
BEGIN
  IF (NEW.cust_no IS NULL) THEN
    NEW.cust_no = GEN_ID(cust_no_gen, 1);
END

См. также:

CREATE TRIGGER,DROP TRIGGER,CREATE OR ALTER TRIGGER.

Триггеры На События Изменения Метаданных

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

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

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

Отключение триггеров

Кто может создать триггеры на события изменения метаданных?

Примеры

ALTER TRIGGER

Допустимые изменения

Кто может изменить триггеры?

Примеры

CREATE OR ALTER TRIGGER

Примеры

DROP TRIGGER

Кто может удалить триггеры?

Примеры

RECREATE TRIGGER

Примеры

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

`ALTER TRIGGER`

`CREATE OR ALTER TRIGGER`

`DROP TRIGGER`

`RECREATE TRIGGER`