Выполнение Триггеров На Событие Базы Данных И Обработка Исключений

CREATE TRIGGER tr_log_connect
INACTIVE ON CONNECT POSITION 0
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 POSITION 1
AS
BEGIN
  IF ((CURRENT_USER <> 'SYSDBA') AND
      NOT (CURRENT_TIME BETWEEN time '9:00' AND time '17:00')) THEN
     EXCEPTION E_INCORRECT_WORKTIME;
END

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 ;!

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 ;!

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 set_cust_no INACTIVE;

ALTER TRIGGER set_cust_no POSITION 14;

ALTER TRIGGER TR_CUST_LOG
INACTIVE AFTER INSERT OR UPDATE;

ALTER TRIGGER TR_CUST_LOG
SQL SECURITY DEFINER;

ALTER TRIGGER TR_CUST_LOG
DROP SQL SECURITY;

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 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

DROP TRIGGER set_cust_no;

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