RDB$TRIGGERS

Версии сервера

Доступно в

DSQL, ESQL, ISQL, PSQL

Формат

CREATE TABLE RDB$TRIGGERS (
    RDB$TRIGGER_NAME      CHAR(31) CHARACTER SET UNICODE_FSS,
    RDB$RELATION_NAME     CHAR(31) CHARACTER SET UNICODE_FSS,
    RDB$TRIGGER_SEQUENCE  SMALLINT,
    RDB$TRIGGER_TYPE      SMALLINT,
    RDB$TRIGGER_SOURCE    BLOB SUB_TYPE 1 SEGMENT SIZE 80 CHARACTER SET UNICODE_FSS,
    RDB$TRIGGER_BLR       BLOB SUB_TYPE 2 SEGMENT SIZE 80,
    RDB$DESCRIPTION       BLOB SUB_TYPE 1 SEGMENT SIZE 80 CHARACTER SET UNICODE_FSS,
    RDB$TRIGGER_INACTIVE  SMALLINT,
    RDB$SYSTEM_FLAG       SMALLINT,
    RDB$FLAGS             SMALLINT,
    RDB$VALID_BLR         SMALLINT,
    RDB$DEBUG_INFO        BLOB SUB_TYPE 9 SEGMENT SIZE 80
);

CREATE INDEX RDB$INDEX_38 ON RDB$TRIGGERS (RDB$RELATION_NAME);
CREATE UNIQUE INDEX RDB$INDEX_8 ON RDB$TRIGGERS (RDB$TRIGGER_NAME);

Описание

Системная таблица RDB$TRIGGERS содержит информацию о всех триггерах в базе данных.

Определены следующие типы триггеров по событию:

Пример

Следующий пример выводит список всех триггеров для таблицы MY_TABLE

SELECT T.RDB$TRIGGER_NAME, T.RDB$TRIGGER_SEQUENCE,
    CASE T.RDB$TRIGGER_TYPE
      WHEN   1 THEN 'BEFORE INSERT'
      WHEN   2 THEN 'AFTER INSERT'
      WHEN   3 THEN 'BEFORE UPDATE'
      WHEN   4 THEN 'AFTER UPDATE'
      WHEN   5 THEN 'BEFORE DELETE'
      WHEN   6 THEN 'AFTER DELETE'
      WHEN  17 THEN 'BEFORE INSERT OR UPDATE'
      WHEN  18 THEN 'AFTER INSERT OR UPDATE'
      WHEN  25 THEN 'BEFORE INSERT OR DELETE'
      WHEN  26 THEN 'AFTER INSERT OR DELETE'
      WHEN  27 THEN 'BEFORE UPDATE OR DELETE'
      WHEN  28 THEN 'AFTER UPDATE OR DELETE'
      WHEN 113 THEN 'BEFORE INSERT OR UPDATE OR DELETE'
      WHEN 114 THEN 'AFTER INSERT OR UPDATE OR DELETE'
    END AS TRIGGER_TYPE
FROM  RDB$TRIGGERS T
WHERE (T.RDB$RELATION_NAME = 'MY_TABLE')

Следующий PSQL-блок вернет DSQL скрипт создания триггера, имя которого передано в качестве параметра TRIGGER_NAME.

EXECUTE BLOCK(
  A_TRIGGER_NAME VARCHAR(31) = ?TRIGGER_NAME
)RETURNS(
  DDLSQL BLOB SUB_TYPE 1 SEGMENT SIZE 80 CHARACTER SET UNICODE_FSS
)AS
  DECLARE VARIABLE P_TRIGGER_NAME   VARCHAR(31);
  DECLARE VARIABLE P_RELATION_NAME  VARCHAR(31);
  DECLARE VARIABLE P_TRIGGER_TYPE   VARCHAR(80);
  DECLARE VARIABLE P_TRIGGER_ACTIVE VARCHAR(8);
  DECLARE VARIABLE P_TRIGGER_POS    INTEGER;
  DECLARE VARIABLE P_TRIGGER_SRC    BLOB SUB_TYPE 1 SEGMENT SIZE 80
                                          CHARACTER SET UNICODE_FSS;
BEGIN
  DDLSQL = '';
  SELECT FIRST 1 TRIM(T.RDB$TRIGGER_NAME), TRIM(T.RDB$RELATION_NAME),
      T.RDB$TRIGGER_SEQUENCE, T.RDB$TRIGGER_SOURCE,
      CASE T.RDB$TRIGGER_INACTIVE
        WHEN 1 THEN 'INACTIVE'
        WHEN 0 THEN 'ACTIVE'
      END AS TRIG_ACTIVE,
      CASE T.RDB$TRIGGER_TYPE
        WHEN   1 THEN 'BEFORE INSERT'
        WHEN   2 THEN 'AFTER INSERT'
        WHEN   3 THEN 'BEFORE UPDATE'
        WHEN   4 THEN 'AFTER UPDATE'
        WHEN   5 THEN 'BEFORE DELETE'
        WHEN   6 THEN 'AFTER DELETE'
        WHEN  17 THEN 'BEFORE INSERT OR UPDATE'
        WHEN  18 THEN 'AFTER INSERT OR UPDATE'
        WHEN  25 THEN 'BEFORE INSERT OR DELETE'
        WHEN  26 THEN 'AFTER INSERT OR DELETE'
        WHEN  27 THEN 'BEFORE UPDATE OR DELETE'
        WHEN  28 THEN 'AFTER UPDATE OR DELETE'
        WHEN 113 THEN 'BEFORE INSERT OR UPDATE OR DELETE'
        WHEN 114 THEN 'AFTER INSERT OR UPDATE OR DELETE'
      END AS TRIGGER_TYPE
  FROM  RDB$TRIGGERS T
  WHERE (UPPER(TRIM(T.RDB$TRIGGER_NAME)) =  :A_TRIGGER_NAME)
  INTO  :P_TRIGGER_NAME, :P_RELATION_NAME, :P_TRIGGER_POS, :P_TRIGGER_SRC,
        :P_TRIGGER_ACTIVE, :P_TRIGGER_TYPE;

  DDLSQL = 'CREATE OR ALTER TRIGGER '||:P_TRIGGER_NAME||' FOR '||
    :P_RELATION_NAME||' '||ASCII_CHAR(13)||ASCII_CHAR(10)||
    :P_TRIGGER_ACTIVE||'  '||:P_TRIGGER_TYPE||' POSITION '||:P_TRIGGER_POS||
    ' '||ASCII_CHAR(13)||ASCII_CHAR(10)||:P_TRIGGER_SRC;

  SUSPEND;
END

См. также

Системные таблицы, CREATE TRIGGER, DROP TRIGGER, ALTER TRIGGER

Источник

✏️ Править эту статью на GitHub