Шаг 118 - PL/SQL - Триггеры - ЧАСТЬ VI - системные триггеры

Уложились в голове все формулировки предыдущих шагов? Думаю да! Теперь самое время для завершающего этапа в плане изучения триггеров. Осталось рассмотреть, так называемые системные триггеры. Итак! Как было сказано выше триггеры DML срабатывают на события DML или вместо них! А, именно на операторы INSERT, UPDATE, DELETE. Но это еще не все события БД, на основе которых можно писать триггеры. В БД существует два основных вида событий, на которые активизируются системные триггеры. А, вернее сказать на события DDL или собственно самой БД. К событиям DDL относятся операторы CREATE, DROP, ALTER. А вот к событиям базы данных - запуск останов сервера, регистрация отключение пользователя БД, ошибка сервера. Рассмотрим синтаксис создания системного триггера. А именно:

--------- CREATE OR REPLACE TRIGGER [схема.]имя_триггера ----------------------------
--------- {BEFORE | AFTER} ----------------------------------------------------------
--------- {список_событий_DDL | список_событий_базы_данных} -------------------------
--------- ON {DATABASE | [схема.]SCHEMA} --------------------------------------------
--------- конструкция_REFERENCING ---------------------------------------------------
--------- [условие_WHEN] ------------------------------------------------------------
--------- тело триггера; ------------------------------------------------------------

Где:

Приведем в виде таблички события и их обработку для БД:

СобытиеРазрешенное время выполненияОписание
STARTUPAFTERАктивизируется после запуска экземпляра
SHUTDOWNBEFOREАктивизируется при остановке экземпляра. Для заметки, это событие не активизирует триггер, если останов БД аварийный!
SERVERERRORAFTERАктивизируется при возникновении ошибки.
LOGONAFTERАктивизируется после успешного соединения пользователя с базой данных.
LOGOOFFBEFOREАктивизируется в начале отключения пользователя.
CREATEBEFORE, AFTERАктивизируется до и после создания объекта схемы.
DROPBEFORE, AFTERАктивизируется до, и после удаления объекта схемы.
ALTERBEFORE, AFTERАктивизируется до и после изменения объекта схемы.

Сразу следует запомнить, что создание триггеров БД требует системных привилегий администратора БД! И создавать их следует внимательно и без спешки! Иначе последствия могут быть очень печальными! Либо вы больше не сможете получить логин к БД, либо вообще потеряете экземпляр! Возможно все! Все действия с полномочиями схем SYS и SYSTEM следует выполнять, имея крепкие и прочные знания и нервы! К чему я собственно вас и веду! Итак, системные триггеры могут создаваться на уровне схемы (SCHEMA) или уровне БД (DATABASE)! Триггеры БД реагируют на события в БД - DATABASE, а схемы - SCHEMA, в которой происходит событие. Что ж, готовы! Хотите попробовать создать системный аудит! Кто боится пусть не делает! Итак, запускаем *SQL/Plus с правами SYSTEM. Создадим табличку для фиксации регистрации пользователей на уровне БД:

CREATE TABLE SYSTEM.AUDTBASE
(
	NZAP NUMBER,
	POLZ VARCHAR2(20),
	TMIN DATE,
	OPER VARCHAR2(50)
)
/

Получаем:

SQL> CREATE TABLE SYSTEM.AUDTBASE
  2  (
  3   NZAP NUMBER,
  4   POLZ VARCHAR2(20),
  5   TMIN DATE,
  6   OPER VARCHAR2(50)
  7  )
  8  /

Таблица создана.

Теперь создадим триггер уровня БД:

CREATE OR REPLACE TRIGGER FIXUSERIN
	AFTER LOGON ON DATABASE

BEGIN

INSERT INTO SYSTEM.AUDTBASE(NZAP, POLZ, TMIN, OPER)
		VALUES(1, USER, SYSDATE, 'UserIsLog(off)');

END FIXUSERIN;
/

Получаем:

SQL> CREATE OR REPLACE TRIGGER FIXUSERIN
  2   AFTER LOGON ON DATABASE
  3  
  4  BEGIN
  5  
  6  INSERT INTO SYSTEM.AUDTBASE(NZAP, POLZ, TMIN, OPER)
  7    VALUES(1, USER, SYSDATE, 'UserIsLog(off)');
  8  
  9  END FIXUSERIN;
 10  /

Триггер создан.

Просто, но со вкусом, теперь запустите еще один *SQL/Plus и посмотрите содержимое таблицы в первом сеансе:

SELECT NZAP, POLZ, TO_CHAR(TMIN,'DD.MM.YYYY HH24:MI:SS'), OPER FROM SYSTEM.AUDTBASE
/

У меня получилось примерно следующее:

SQL> SELECT NZAP, POLZ, TO_CHAR(TMIN,'DD.MM.YYYY HH24:MI:SS'), OPER FROM SYSTEM.AUDTBASE
  2  /

      NZAP POLZ            TO_CHAR(TMIN,'DD.MM.YYYYHH24:M OPER
---------- --------------- ------------------------------ --------------------
         1 SYS             22.03.2003 16:35:50            UserIsLog(off)
         1 SYS             22.03.2003 16:35:50            UserIsLog(off)
         1 SYS             22.03.2003 16:37:38            UserIsLog(off)
         1 MILLER          22.03.2003 16:38:44            UserIsLog(off)
         1 MILLER          22.03.2003 16:38:44            UserIsLog(off)
         1 MILLER          22.03.2003 16:38:46            UserIsLog(off)

6 строк выбрано.

У вас может быть по другому, но суть остается той же! Вот такие дела! Уже получается что-то серьезное! Для полноты картины пока удалите триггер FIXUSERIN и табличку AUDTBASE, только лучше сначала триггер, а потом таблицу! То же можно проделать и для схемы MILLER примерно так:

CREATE TABLE MILLER.AUDTBASE
(
	NZAP NUMBER,
	POLZ VARCHAR2(20),
	TMIN DATE,
	OPER VARCHAR2(50)
)
/

CREATE OR REPLACE TRIGGER FIXUSERIN
	AFTER LOGON ON SCHEMA

BEGIN

INSERT INTO SYSTEM.AUDTBASE(NZAP, POLZ, TMIN, OPER)
		VALUES(1, USER, SYSDATE, 'UserIsLog(off)');

END FIXUSERIN;
/

Проделайте все сами и убедитесь, что события будут теперь срабатывать только в вашей конкретной схеме, а не как было ранее. Так же для информации замечу, что триггеры STARTUP и SHUTDOWN имеют смысл только на уровне БД, хотя их можно создать и в конкретной схеме, но активизироваться они не будут! Кроме того, для системных триггеров существует ряд атрибутных функций. Помните INSERTING и т. д. эти функции имеют тот же смысл! Вот их описание:

Атрибутная функцияТип данныхСистемное событие для которых применяетсяОписание
SYSEVENTVARCHAR2(20)Все событияВозвращает системное событие активизировавшее триггер
INSTANCE_NUMNUMBERВсе событияВозвращает номер текущего экземпляра
DATABASE_NAMEVARCHAR2(50)Все событияВозвращает имя текущей БАЗЫ ДАННЫХ.
SERVER_ERRORNUMBERSERVERERRORПринимает один числовой аргумент. Возвращает ошибку на позиции в стеке ошибок, указанной аргументом. Вершина стека позиция -1
IS_SERVERERRORBOOLEANSERVERERRORПринимает номер ошибки в качестве аргумента и возвращает TRUE, если указанная ошибка Oracle присутствует в стеке ошибок.
LOGIN_USERVARCHAR2(20)Все событияВозвращает идентификатор пользователя активизирующего триггер.
DICTIONARY_OBJ_TYPEVARCHAR2(30)CREATE, DROP, ALTERВозвращает тип объекта словаря, над которым выполнялась операция DDL активизировавшая триггер.
DICTIONARY_OBJ_NAMEVARCHAR2(30)CREATE, DROP, ALTERВозвращает имя объекта словаря, над которым выполнялась операция DDL активизировавшая триггер.
DICTIONARY_OBJ_OWNERVARCHAR2(30)CREATE, DROP, ALTERВозвращает владельца того объекта словаря, над которым выполнялась операция DDL активизировавшая триггер.
DES_ENCRYPTED_PASSWORDVARCHAR2(30)CREATE USER, ALTER USERВозвращает зашифрованный в стандарте DES пароль создаваемого или изменяемого пользователя.

Ура! Наконец тему триггеры мы закрываем! Я опустил пока тематику триггеров типа INSTEAD OF для представлений, но если таковая необходимость есть, я могу их описать и привести примеры. Или мы еще вернемся к ним позже! А пока можете закреплять материал. Например, вам задание напишите два, три системных триггера уровня схемы, (с уровнем БД советую быть осторожнее) применив некоторые предикаты описанные выше. Например, я считаю очень интересным атрибутную функцию DES_ENCRYPTED_PASSWORD - кто знает алгоритмы DES можете потом расшифровать! Пробуйте и спрашивайте, если что-то до конца не ясно! Удачи! :)


Предыдущий Шаг | Следующий Шаг | Оглавление
Автор Летучий Сергей.