Шаг 111 - PL/SQL - Триггеры таблиц БД, операторный триггер

Кое, что о триггерах мы уже знаем. В теории. Давайте попробуем на практике. Запускаем наш "веселый" 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 далее она нам не понадобиться! Как это делать вы уже знаете или нет? Работайте!


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