Уложились в голове все формулировки предыдущих шагов? Думаю да! Теперь самое время для завершающего этапа в плане изучения триггеров. Осталось рассмотреть, так называемые системные триггеры. Итак! Как было сказано выше триггеры DML срабатывают на события DML или вместо них! А, именно на операторы INSERT, UPDATE, DELETE. Но это еще не все события БД, на основе которых можно писать триггеры. В БД существует два основных вида событий, на которые активизируются системные триггеры. А, вернее сказать на события DDL или собственно самой БД. К событиям DDL относятся операторы CREATE, DROP, ALTER. А вот к событиям базы данных - запуск останов сервера, регистрация отключение пользователя БД, ошибка сервера. Рассмотрим синтаксис создания системного триггера. А именно:
--------- CREATE OR REPLACE TRIGGER [схема.]имя_триггера ----------------------------
--------- {BEFORE | AFTER} ----------------------------------------------------------
--------- {список_событий_DDL | список_событий_базы_данных} -------------------------
--------- ON {DATABASE | [схема.]SCHEMA} --------------------------------------------
--------- конструкция_REFERENCING ---------------------------------------------------
--------- [условие_WHEN] ------------------------------------------------------------
--------- тело триггера; ------------------------------------------------------------
Где:
Приведем в виде таблички события и их обработку для БД:
| Событие | Разрешенное время выполнения | Описание |
|---|---|---|
| STARTUP | AFTER | Активизируется после запуска экземпляра |
| SHUTDOWN | BEFORE | Активизируется при остановке экземпляра. Для заметки, это событие не активизирует триггер, если останов БД аварийный! |
| SERVERERROR | AFTER | Активизируется при возникновении ошибки. |
| LOGON | AFTER | Активизируется после успешного соединения пользователя с базой данных. |
| LOGOOFF | BEFORE | Активизируется в начале отключения пользователя. |
| CREATE | BEFORE, AFTER | Активизируется до и после создания объекта схемы. |
| DROP | BEFORE, AFTER | Активизируется до, и после удаления объекта схемы. |
| ALTER | BEFORE, 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 и т. д. эти функции имеют тот же смысл! Вот их описание:
| Атрибутная функция | Тип данных | Системное событие для которых применяется | Описание |
|---|---|---|---|
| SYSEVENT | VARCHAR2(20) | Все события | Возвращает системное событие активизировавшее триггер |
| INSTANCE_NUM | NUMBER | Все события | Возвращает номер текущего экземпляра |
| DATABASE_NAME | VARCHAR2(50) | Все события | Возвращает имя текущей БАЗЫ ДАННЫХ. |
| SERVER_ERROR | NUMBER | SERVERERROR | Принимает один числовой аргумент. Возвращает ошибку на позиции в стеке ошибок, указанной аргументом. Вершина стека позиция -1 |
| IS_SERVERERROR | BOOLEAN | SERVERERROR | Принимает номер ошибки в качестве аргумента и возвращает TRUE, если указанная ошибка Oracle присутствует в стеке ошибок. |
| LOGIN_USER | VARCHAR2(20) | Все события | Возвращает идентификатор пользователя активизирующего триггер. |
| DICTIONARY_OBJ_TYPE | VARCHAR2(30) | CREATE, DROP, ALTER | Возвращает тип объекта словаря, над которым выполнялась операция DDL активизировавшая триггер. |
| DICTIONARY_OBJ_NAME | VARCHAR2(30) | CREATE, DROP, ALTER | Возвращает имя объекта словаря, над которым выполнялась операция DDL активизировавшая триггер. |
| DICTIONARY_OBJ_OWNER | VARCHAR2(30) | CREATE, DROP, ALTER | Возвращает владельца того объекта словаря, над которым выполнялась операция DDL активизировавшая триггер. |
| DES_ENCRYPTED_PASSWORD | VARCHAR2(30) | CREATE USER, ALTER USER | Возвращает зашифрованный в стандарте DES пароль создаваемого или изменяемого пользователя. |
Ура! Наконец тему триггеры мы закрываем! Я опустил пока тематику триггеров типа INSTEAD OF для представлений, но если таковая необходимость есть, я могу их описать и привести примеры. Или мы еще вернемся к ним позже! А пока можете закреплять материал. Например, вам задание напишите два, три системных триггера уровня схемы, (с уровнем БД советую быть осторожнее) применив некоторые предикаты описанные выше. Например, я считаю очень интересным атрибутную функцию DES_ENCRYPTED_PASSWORD - кто знает алгоритмы DES можете потом расшифровать! Пробуйте и спрашивайте, если что-то до конца не ясно! Удачи! :)