Шаг 117 - PL/SQL - Триггеры - ЧАСТЬ V - mutating table

Так как триггеры БД довольно обширная тема, продолжаем разбирать ее далее. Давайте рассмотрим вариант, когда триггер работает с активизирующей его таблицей. Если говорить о работе триггеров таблиц, то как оказывается, триггер может обращаться не ко всем таблицам и столбцам этих таблиц. Для того, чтобы определить к каким таблицам возможен доступ, необходимо понимать, что такое изменяющиеся и ограничивающие таблицы. Изменяющиеся таблица (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 - операторы в теле триггера не могут:

  1. Считывать или модифицировать информацию, любой таблицы изменяющейся в результате выполнения активизирующего оператора. В число таких таблиц входит и сама активизирующая таблица.
  2. Считывать или модифицировать информацию столбца первичного ключа, уникальных столбцов и столбцов внешних ключей таблицы, являющейся ограничивающей по отношению к изменяющейся таблице. (уфф .. запутанная формулировочка! Но верная!).

Заметим, что эти правила верны для строковых триггеров. Для операторных триггеров они применимы только в тех случаях, когда последние активизируются в результате выполнения операции каскадного удаления информации. Следует так же сказать, что оператор 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 успешно завершена.

Вот здесь ясно видно, что вся работа триггеров прошла успешно и больше здесь комментировать нечего! Думаю, теперь вам ясно, как избежать ошибок при написании триггеров при использовании изменяющихся таблиц и основные правила работы с ними. Если что-то не совсем ясно, пишите, буду рад ответить!


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