Oracle поддерживает ряд команд DDL, повышающих эффективность управления триггерами на PL/SQL. С их помощью можно включать, отключать и удалять триггеры, просматривать информацию о них, проверять их состояние.
Отключение, включение и удаление триггеров в PL/SQL
Отключенный триггер не запускается при наступлении связанного с ним события. Удаленный триггер полностью исчезает из базы данных. Синтаксис отключения триггера очень прост по сравнению с синтаксисом создания:
ALTER TRIGGER имя_триггера DISABLE;
Пример:
ALTER TRIGGER emp_after_insert DISABLE;
Отключенный триггер можно включить повторно следующей командой:
ALTER TRIGGER emp_after_insert ENABLE;
В команде ALTER TRIGGER
задается только имя триггера; ни его тип, ни что-либо иное задавать не нужно. Эту команду можно вызывать из хранимой процедуры PL/SQL, как в следующем примере, где включение и отключение всех триггеров таблицы выполняется динамическим кодом SQL:
PROCEDURE settrig (
tab IN VARCHAR2
, sch IN VARCHAR DEFAULT NULL
, action IN VARCHAR2
)
IS
l_action VARCHAR2 (10) := UPPER (action);
l_other_action VARCHAR2 (10) := 'DISABLED';
BEGIN
IF l_action = 'DISABLE'
THEN
l_other_action := 'ENABLED';
END IF;
FOR rec IN (SELECT trigger_name FROM user_triggers
WHERE table_owner = UPPER (NVL (sch, USER))
AND table_name = tab AND status = l_other_action)
LOOP
EXECUTE IMMEDIATE
'ALTER TRIGGER ' || rec.trigger_name || ' ' || l_action;
END LOOP;
END;
Команда удаления триггера DROP TRIGGER
столь же проста; как и в предыдущем случае, достаточно указать имя:
DROP TRIGGER emp_after_insert;
Создание отключенных триггеров PL/SQL
В Oracle11g появилась возможность создания триггеров в отключенном состоянии. Например, это может быть удобно, если вы хотите проверить правильность триггера без его запуска. Очень простой пример:
TRIGGER just_testing
AFTER INSERT ON abc
DISABLE
BEGIN
NULL;
END;
Благодаря присутствию в заголовке ключевого слова DISABLE
этот триггер будет проверен, откомпилирован и создан, но не будет запускаться до его явного включения на более поздней стадии. Учтите, что ключевое слово DISABLE
не сохраняется в базе данных:
SQL> SELECT trigger_body
2 FROM user_triggers
3 WHERE trigger_name = 'JUST_TESTING';
TRIGGER_BODY
------------------------------------------------
BEGIN
NULL;
END;
Если вы пользуетесь служебными программами с графическим интерфейсом, будьте осторожны — перекомпиляция может привести к непреднамеренному включению триггеров.
Просмотр триггеров PL/SQL
В словаре данных Oracle имеется несколько представлений, возвращающих разнообразную информацию о триггерах:
- DBA_TRIGGERS — все триггеры базы данных;
- ALL_TRIGGERS — все триггеры, доступные текущему пользователю;
- USER_TRIGGERS — все триггеры, принадлежащие текущему пользователю.
В табл. 1 перечислены самые важные (и часто используемые) столбцы этих представлений.
Таблица 1. Название таблицы
имя | описание |
TRIGGER_NAME | Имя триггера |
TRIGGER_TYPE | Тип триггера: для триггеров DML–BEFORE_STATEMENT, BEFORE EACH ROW, AFTER EACH ROW или AFTER STATEMENT; для триггеров DDL — BEFORE EVENT или AFTER EVENT; для триггеров INSTEAD OF — INSTEAD OF; для триггеров AFTER_SUSPEND — AFTER EVENT |
TRIGGERING_EVENT | Событие, вызвавшее запуск триггера: для триггеров DML — UPDATE, INSERT или DELETE; для триггеров DDL — операция DDL (см. список в разделе, по- священном триггерам данного типа); для триггеров событий базы данных — ERROR, LOGON, LOGOFF, STARTUP или SHUTDOWN; для триггеров INSTEAD OF — INSERT, UPDATE или DELETE; для триггеров AFTER SUSPEND — SUSPEND |
TABLE_OWNER | Различная информация в зависимости от типа триггера: для триггеров DML имя владельца таблицы, с которой связан триггер; для триггеров DDL при условии, что это триггер уровня базы данных, — SYS, в противном случае — имя владельца триггера; для триггеров событий базы данных при условии, что это триггеры уровня базы данных, — SYS, в противном случае — имя владельца триггера; для триггеров INSTEAD OF — имя владельца представления, с которым связан данный триггер; для триггеров AFTER SUSPEND при условии, что это триггеры уровня базы данных, — SYS, в противном случае — имя владельца триггера |
BASE_OBJECT_TYPE | Тип объекта, с которым связан триггер: для триггеров DML — TABLE; для триг- геров DDL — SCHEMA или DATABASE; для триггеров событий базы данных — SCHEMA или DATABASE; для триггеров INSTEAD OF — VIEW; для триггеров AFTER SUSPEND — SCHEMA или DATABASE |
TABLE_NAME | Для триггеров DML — имя таблицы, с которой связан триггер; для остальных типов триггеров — значение NULL |
REFERENCING_NAMES | Для триггеров DML (уровня строки) — предложение, используемое для определения псевдонимов записей OLD и NEW; для остальных типов триггеров —текст «REFERENCING NEW AS NEW OLD AS OLD» |
WHEN_CLAUSE | Для триггеров DML — предложение с условием выполнения триггера |
STATUS | Состояние триггера (ENABLED или DISABLED) |
ACTION_TYPE | Признак того, выполняет ли триггер вызов (CALL) или содержит код PL/SQL (PL/SQL) |
TRIGGER_BODY | Текст тела триггера (столбец типа LONG); начиная с Oracle9i эта информация также присутствует в таблице USER_SOURCE |
Проверка работоспособности триггера
Как ни странно, ни одно из указанных представлений не содержит информации о том, находится ли триггер в работоспособном состоянии. Если триггер создан ошибочной командой PL/SQL, он сохраняется в базе данных, но помечается как неработоспособный (INVALID
). Чтобы определить, находится ли триггер в этом состоянии, нужно извлечь информацию из представления user_objects
или all_objects
:
SQL> CREATE OR REPLACE TRIGGER invalid_trigger
2 AFTER DDL ON SCHEMA
3 BEGIN
4 NULL
5 END;
6 /
Warning: Trigger created with compilation errors.
SQL> SELECT object_name,
2 object_type,
3 status
4 FROM user_objects
5 WHERE object_name = 'INVALID_TRIGGER';
OBJECT_NAME OBJECT TYPE STATUS
------------- ----------- -------
INVALID_TRIGGER TRIGGER INVALID