Так как триггеры БД довольно обширная тема, продолжаем разбирать ее далее. Давайте рассмотрим вариант, когда триггер работает с активизирующей его таблицей. Если говорить о работе триггеров таблиц, то как оказывается, триггер может обращаться не ко всем таблицам и столбцам этих таблиц. Для того, чтобы определить к каким таблицам возможен доступ, необходимо понимать, что такое изменяющиеся и ограничивающие таблицы. Изменяющиеся таблица (mutating table) - это именно та таблица, которая в данный момент модифицируется оператором DML! Для триггера это та таблица, для которой он был создан! Так же и те таблицы, которые обновляются в результате реализации или действия ссылочной целостности. Таблицы выполняющие каскадные удаления - DELETE CASCADE, так же являются изменяющимися. Ограничивающая таблица (constraining table) - это таблица, информация которой может быть считана при реализации ограничений ссылочной целостности. Вот такие трудности могут быть на пути создания триггеров БД. Давайте рассмотрим пример "неверного" триггера БД. Запишем вот такой триггер для таблицы TSTTRIG:
CREATE OR REPLACE TRIGGER ERRTRIG AFTER INSERT ON TSTTRIG FOR EACH ROW BEGIN UPDATE TSTTRIG SET ROD = LOWER(ROD) WHERE ID = :old.ID; END ERRTRIG; /
Получаем:
SQL> CREATE OR REPLACE TRIGGER ERRTRIG 2 AFTER INSERT ON TSTTRIG 3 FOR EACH ROW 4 5 BEGIN 6 7 UPDATE TSTTRIG 8 SET ROD = LOWER(ROD) 9 WHERE ID = :old.ID; 10 11 END ERRTRIG; 12 / Триггер создан.
Здесь для простоты примера я произвожу модификацию столбца ROD после вставки строки. Пробуем добавить, запись в таблицу TSTTRIG:
INSERT INTO TSTTRIG (NM, ROD, INRW)
VALUES ('BOB', 'DUMMY', TO_DATE('18-12-2004', 'DD-MM-YYYY'))
/
Получаем:
SQL> INSERT INTO TSTTRIG (NM, ROD, INRW)
2 VALUES ('BOB', 'DUMMY', TO_DATE('18-12-2004', 'DD-MM-YYYY'))
3 /
INSERT INTO TSTTRIG (NM, ROD, INRW)
*
ошибка в строке 1:
ORA-04091: таблица MILLER.TSTTRIG изменяется, триггер/функция может не заметить
это
ORA-06512: на "MILLER.ERRTRIG", line 5
ORA-04088: ошибка во время выполнения триггера 'MILLER.ERRTRIG'
Как видно сразу три типа ошибки вызывает эта операция! Но основная из них это ORA-04091! То есть для триггера MILLER.TSTTRIG таблица TSTTRIG является изменяющейся и модифицировать ее с помощью операторов DML он не может! Можете удалить триггер MILLER.TSTTRIG, чтобы он нам не мешал. Как это делать я думаю вы знаете! Итак, подведем черту.
SQL - операторы в теле триггера не могут:
Заметим, что эти правила верны для строковых триггеров. Для операторных триггеров они применимы только в тех случаях, когда последние активизируются в результате выполнения операции каскадного удаления информации. Следует так же сказать, что оператор INSERT воздействующий только на одну строку (хм .. ну естественно он же ее добавляет - ведь так? :) ) для строковых триггеров BEFORE и AFTER работающих с этой строкой активизирующая таблица как ни странно не является изменяющейся! Это единственная ситуация когда строковый триггер может считывать или модифицировать информацию активизирующей таблицы. Но! Для таких операторов как:
INSERT INTO таблица SELECT ......
активизирующая таблица всегда является изменяющейся, даже если в подзапросе возвращается только одна строка! Вот так бывает много разных ситуаций, про которые не следует забывать, а просто применять их на практике при создании триггеров для таблиц. А, что если вам все же необходимо получать данные в триггере от самой инициирующей таблицы? Тогда можно поступить следующим образом. Создать два триггера, один операторный другой строчный и производить все необходимые действия с их помощью. Давайте попробуем это проиллюстрировать, но здесь вам нужно будет кое-что вспомнить, если забыли обращайтесь к прошлым шагам там все есть! Итак, начнем с написания такого пакета:
CREATE OR REPLACE PACKAGE TrigTest IS TYPE m_ID IS TABLE OF TSTTRIG.ID%TYPE INDEX BY BINARY_INTEGER; TYPE m_NM IS TABLE OF TSTTRIG.NM%TYPE INDEX BY BINARY_INTEGER; V_m_ID m_ID; V_m_NM m_NM; V_num BINARY_INTEGER := 0; END TrigTest; /
Получаем:
SQL> CREATE OR REPLACE PACKAGE TrigTest IS 2 3 TYPE m_ID IS TABLE OF TSTTRIG.ID%TYPE 4 INDEX BY BINARY_INTEGER; 5 6 TYPE m_NM IS TABLE OF TSTTRIG.NM%TYPE 7 INDEX BY BINARY_INTEGER; 8 9 V_m_ID m_ID; 10 V_m_NM m_NM; 11 V_num BINARY_INTEGER := 0; 12 13 END TrigTest; 14 / Пакет создан.
Здесь мы создали две таблицы V_m_ID и V_m_NM и переменную V_num для обращения к записям таблиц. Все достаточно не сложно! Теперь создадим такой строчный триггер:
CREATE OR REPLACE TRIGGER FIXTRG BEFORE INSERT OR UPDATE OF NM ON TSTTRIG FOR EACH ROW BEGIN TrigTest.V_num := TrigTest.V_num +1; TrigTest.V_m_ID(TrigTest.V_num) := :new.ID; TrigTest.V_m_NM(TrigTest.V_num) := :new.NM; END FIXTRG; /
Получаем:
SQL> CREATE OR REPLACE TRIGGER FIXTRG 2 BEFORE INSERT OR UPDATE OF NM ON TSTTRIG 3 FOR EACH ROW 4 5 BEGIN 6 7 TrigTest.V_num := TrigTest.V_num +1; 8 TrigTest.V_m_ID(TrigTest.V_num) := :new.ID; 9 TrigTest.V_m_NM(TrigTest.V_num) := :new.NM; 10 11 END FIXTRG; 12 / Триггер создан.
Этот триггер фиксирует значения двух полей таблицы TSTTRIG в наших пакетных таблицах V_m_ID и V_m_NM. Далее создаем вот такой операторный триггер:
CREATE OR REPLACE TRIGGER OPERTRG BEFORE INSERT OR UPDATE OF NM ON TSTTRIG DECLARE V_CNT NUMBER; V_COUNT CONSTANT NUMBER := 10; BEGIN SELECT COUNT(*) INTO V_CNT FROM TSTTRIG; IF( TrigTest.V_num = V_COUNT ) THEN TrigTest.V_m_ID(TrigTest.V_num) := V_CNT; TrigTest.V_m_NM(TrigTest.V_num) := 'STOP'; TrigTest.V_num := 0; END IF; END OPERTRG; /
Получаем:
SQL> CREATE OR REPLACE TRIGGER OPERTRG 2 BEFORE INSERT OR UPDATE OF NM ON TSTTRIG 3 4 DECLARE 5 6 V_CNT NUMBER; 7 V_COUNT CONSTANT NUMBER := 10; 8 9 BEGIN 10 11 SELECT COUNT(*) INTO V_CNT 12 FROM TSTTRIG; 13 14 IF( TrigTest.V_num = V_COUNT ) THEN 15 TrigTest.V_m_ID(TrigTest.V_num) := V_CNT; 16 TrigTest.V_m_NM(TrigTest.V_num) := 'STOP'; 17 TrigTest.V_num := 0; 18 END IF; 19 20 21 END OPERTRG; 22 / Триггер создан.
Здесь саму идею я высосал из пальца, собственно триггер ничего путного не производит, а только фиксирует количество записей в таблице TSTTRIG в пакетных таблицах (запутался совсем в таблицах)! Но здесь явно видно, что триггер OPERTRG делает запрос к инициирующей таблице и это ему сходит с рук! Что собственно и требовалось показать! Сами можете придумать что-то более путное, но моя задача состоит в том, чтобы Вы поняли как это все происходит или запутались окончательно! Но, думаю сможете все это разобрать! Теперь давайте попробуем поизменять поля таблицы TSTTRIG два три раза:
UPDATE TSTTRIG SET NM = 'ALF' WHERE NM = 'BOB' / UPDATE TSTTRIG SET NM = 'BOB' WHERE NM = 'ALF' / UPDATE TSTTRIG SET NM = 'BUBER' WHERE NM = 'SCOTT' / UPDATE TSTTRIG SET NM = 'SCOTT' WHERE NM = 'BUBER' / COMMIT /
Получаем:
SQL> UPDATE TSTTRIG 2 SET NM = 'ALF' 3 WHERE NM = 'BOB' 4 / 1 строка обновлена. SQL> UPDATE TSTTRIG 2 SET NM = 'BOB' 3 WHERE NM = 'ALF' 4 / 1 строка обновлена. SQL> UPDATE TSTTRIG 2 SET NM = 'BUBER' 3 WHERE NM = 'SCOTT' 4 / 1 строка обновлена. SQL> UPDATE TSTTRIG 2 SET NM = 'SCOTT' 3 WHERE NM = 'BUBER' 4 / 1 строка обновлена. SQL> COMMIT 2 / Фиксация обновлений завершена.
А затем напишем вот такой неименованный блок и посмотрим, что он нам выдаст по части содержимого пакетных табличек:
SET SERVEROUTPUT ON DECLARE BEGIN FOR V_LOOP IN 1..TrigTest.V_m_NM.COUNT LOOP DBMS_OUTPUT.enable; DBMS_OUTPUT.put_line(TO_CHAR(TrigTest.V_m_ID(V_LOOP))); DBMS_OUTPUT.put_line(TrigTest.V_m_NM(V_LOOP)); END LOOP; END; /
Получаем:
SQL> SET SERVEROUTPUT ON SQL> DECLARE 2 3 BEGIN 4 5 FOR V_LOOP IN 1..TrigTest.V_m_NM.COUNT LOOP 6 7 DBMS_OUTPUT.enable; 8 DBMS_OUTPUT.put_line(TO_CHAR(TrigTest.V_m_ID(V_LOOP))); 9 DBMS_OUTPUT.put_line(TrigTest.V_m_NM(V_LOOP)); 10 11 END LOOP; 12 13 END; 14 / 8041 ALF 8041 BOB 7372 BUBER 7372 SCOTT 8041 ALF 8041 BOB 7372 BUBER 7372 SCOTT Процедура PL/SQL успешно завершена.
Вот здесь ясно видно, что вся работа триггеров прошла успешно и больше здесь комментировать нечего! Думаю, теперь вам ясно, как избежать ошибок при написании триггеров при использовании изменяющихся таблиц и основные правила работы с ними. Если что-то не совсем ясно, пишите, буду рад ответить!