Кое, что о триггерах мы уже знаем. В теории. Давайте попробуем на практике. Запускаем наш "веселый" SQL*Plus! Для демонстрации нам потребуется вспомогательная табличка, вот такого вида:
CREATE TABLE MILLER.ADT ( USAL VARCHAR2(50), TISP DATE ) /
Создадим ее:
SQL> CREATE TABLE MILLER.ADT 2 ( 3 USAL VARCHAR2(50), 4 TISP DATE 5 ) 6 / Таблица создана.
Теперь попробуем с помощью нее и нового триггера организовать некий аудит в системе доступа к таблице MILLER.CUSTOMERSS. Для этого создадим простой операторный триггер:
CREATE OR REPLACE TRIGGER testTrg AFTER INSERT OR DELETE OR UPDATE ON customers DECLARE BEGIN INSERT INTO MILLER.ADT(USAL, TISP) VALUES(USER, SYSDATE); END testTrg; /
Откомпилируем его:
SQL> CREATE OR REPLACE TRIGGER testTrg 2 AFTER INSERT OR DELETE OR UPDATE ON customers 3 4 DECLARE 5 6 BEGIN 7 8 INSERT INTO MILLER.ADT(USAL, TISP) 9 VALUES(USER, SYSDATE); 10 11 END testTrg; 12 / Триггер создан.
Триггер создан и будет реагировать на события INSERT, DELETE, UPDATE возникающие при работе с таблицей MILLER.CUSTOMERSS после того как они произойдут! Давайте для того, чтобы проверить это проделаем следующее. Добавим запись в таблицу MILLER.CUSTOMERS и затем посмотрим на содержимое таблицы MILLER.ADT. Вот так:
INSERT INTO CUSTOMERS(cust_num, company, cust_rep, credit_limit) VALUES (2200, 'MyCompany', 107, 555.5643) /
Видим:
SQL> INSERT INTO CUSTOMERS(cust_num, company, cust_rep, credit_limit) 2 VALUES (2200, 'MyCompany', 107, 555.5643) 3 / 1 строка создана.
Запись добавлена, замечательно, а что же в таблице аудита MILLER.ADT? Смотрим:
SELECT USAL, TO_CHAR(TISP,'DD/MM/YYYY, HH24:MI:SS') TISM FROM ADT /
Получаем:
SQL> SELECT USAL, TO_CHAR(TISP,'DD/MM/YYYY, HH24:MI:SS') TISM 2 FROM ADT 3 / USAL TISM -------------------------------------------------- -------------------- MILLER 17/03/2004, 15:25:12
Как видно я применил встроенную в PL/SQL функцию TO_CHAR с шаблоном вывода даты и времени такого вида: 'DD/MM/YYYY, HH24:MI:SS'. И действительно 17-го марта в 15:25:12 я добавил запись в таблицу MILLER.CUSTOMERS. Хотя это не совсем и очевидно, но тем не менее. А, что если мы попробуем дать оператор отката транзакции ROLLBACK! Обратите внимание - я не давал оператор COMMIT, но триггер сработал! Собственно это и очевидно! Итак откатимся:
ROLLBACK /
Видим:
SQL> ROLLBACK 2 / Откат завершен.
Посмотрим в нашу табличку ADT:
SQL> SELECT USAL, TO_CHAR(TISP,'DD/MM/YYYY, HH24:MI:SS') TISM 2 FROM ADT 3 / строки не выбраны
Все откат вернул все на круги своя. Давайте снова добавим запись уже закрепив все оператором COMMIT и пойдем дальше. Итак:
INSERT INTO CUSTOMERS(cust_num, company, cust_rep, credit_limit) VALUES (2200, 'MyCompany', 107, 555.5643) / COMMIT /
Получаем:
SQL> INSERT INTO CUSTOMERS(cust_num, company, cust_rep, credit_limit) 2 VALUES (2200, 'MyCompany', 107, 555.5643) 3 / 1 строка создана. SQL> COMMIT 2 / Фиксация обновлений завершена.
Смотрим табличку ADT:
SELECT USAL, TO_CHAR(TISP,'DD/MM/YYYY, HH24:MI:SS') TISM FROM ADT /
Получаем:
SQL> SELECT USAL, TO_CHAR(TISP,'DD/MM/YYYY, HH24:MI:SS') TISM 2 FROM ADT 3 / USAL TISM -------------------------------------------------- -------------------- MILLER 17/03/2004, 15:29:03
Вот все получилось! Давайте теперь попробуем изменить запись в MILLER.CUSTOMERS, вот таким образом:
UPDATE CUSTOMERS SET cust_num = 2222 WHERE cust_num = 2200 / COMMIT /
Получаем:
SQL> UPDATE CUSTOMERS 2 SET cust_num = 2222 3 WHERE cust_num = 2200 4 / 1 строка обновлена. SQL> COMMIT 2 / Фиксация обновлений завершена.
Посмотрим, что в MILLER.ADT:
SELECT USAL, TO_CHAR(TISP,'DD/MM/YYYY, HH24:MI:SS') TISM FROM ADT /
Получаем:
SQL> SELECT USAL, TO_CHAR(TISP,'DD/MM/YYYY, HH24:MI:SS') TISM 2 FROM ADT 3 / USAL TISM -------------------------------------------------- -------------------- MILLER 17/03/2004, 15:29:03 MILLER 17/03/2004, 15:31:01
Вот теперь добавление записи и ее изменение зафиксировано как факт и никуда не денешься! Давайте удалим теперь лишнюю запись в MILLER.CUSTOMERS. Таким вот образом:
DELETE FROM CUSTOMERS WHERE cust_num = 2222 / COMMIT / Фиксация обновлений завершена.
Получаем:
SQL> DELETE FROM CUSTOMERS 2 WHERE cust_num = 2222 3 / 1 строка удалена. SQL> COMMIT 2 / Фиксация обновлений завершена.
Все удалилось, пробуем смотреть MILLER.ADT:
SQL> SELECT USAL, TO_CHAR(TISP,'DD/MM/YYYY, HH24:MI:SS') TISM 2 FROM ADT 3 / USAL TISM -------------------------------------------------- -------------------- MILLER 17/03/2004, 15:29:03 MILLER 17/03/2004, 15:31:01 MILLER 17/03/2004, 15:31:41
Все три действия с нашей учебной табличкой MILLER.CUSTOMERS были безупречно зафиксированы триггером testTrg. Давайте посмотрим, как определен наш триггер в словаре данных, а именно в представлении USER_TRIGGERS, дадим вот такой запрос:
SELECT TRIGGER_NAME, TRIGGER_TYPE, TABLE_NAME, TRIGGERING_EVENT FROM USER_TRIGGERS WHERE TRIGGER_NAME = 'TESTTRG' /
Получаем:
SQL> SELECT TRIGGER_NAME, TRIGGER_TYPE, TABLE_NAME, TRIGGERING_EVENT 2 FROM USER_TRIGGERS 3 -- WHERE TRIGGER_NAME = 'testTrg' 4 / TRIGGER_NAME TRIGGER_TYPE TABLE_NAME TRIGGERING_EVENT -------------- ---------------- ------------ -------------------------- TESTTRG AFTER STATEMENT CUSTOMERS INSERT OR UPDATE OR DELETE
Обратите внимание, что в условии WHERE, я пишу 'TESTTRG', а не 'testTrg'! Хорошо видно, что весь наш с вами триггер расписан и ясно, что и для чего! Так же если есть необходимость на некоторое время прекратить вызов определенного вами триггера, но не удалять его тело полностью, примените команду:
ALTER TRIGGER TESTTRG DISABLE
Получаем:
SQL> ALTER TRIGGER TESTTRG DISABLE 2 / Триггер изменен.
Если теперь снова добавить запись в MILLER.CUSTOMERS, то в MILLER.ADT ничего нового не появится! Давайте проверим:
INSERT INTO CUSTOMERS(cust_num, company, cust_rep, credit_limit) VALUES (2200, 'MyCompany', 107, 555.5643) / COMMIT /
Получаем:
SQL> INSERT INTO CUSTOMERS(cust_num, company, cust_rep, credit_limit) 2 VALUES (2200, 'MyCompany', 107, 555.5643) 3 / 1 строка создана. SQL> COMMIT 2 / Фиксация обновлений завершена.
Снова смотрим содержимое MILLER.ADT:
SQL> SELECT USAL, TO_CHAR(TISP,'DD/MM/YYYY, HH24:MI:SS') TISM 2 FROM ADT 3 / USAL TISM -------------------------------------------------- -------------------- MILLER 05/03/2004, 15:29:03 MILLER 05/03/2004, 15:31:01 MILLER 05/03/2004, 15:31:41
Три записи и ничего нового! Триггер "спит"! Удалим лишнюю запись из учебной таблички:
DELETE FROM CUSTOMERS WHERE cust_num = 2200 / COMMIT / Фиксация обновлений завершена.
Удалено:
SQL> DELETE FROM CUSTOMERS 2 WHERE cust_num = 2200 3 / 1 строка удалена. SQL> COMMIT 2 / Фиксация обновлений завершена.
И конечно триггер удаляется с помощью оператора DROP TRIGGER, вот таким образом:
DROP TRIGGER TESTTRG /
Получаем:
SQL> DROP TRIGGER TESTTRG 2 / Триггер удален.
Вот и все, кирдык! Мучились, мучились, а в конце концов все убили! Чего у нас такие истории с несчастливыми окончаниями, не пойму! Но я думаю, что для вас это совсем не так! Можете кстати пока удалить и табличку MILLER.ADT далее она нам не понадобиться! Как это делать вы уже знаете или нет? Работайте!