Шаг 113 - PL/SQL - Триггеры - псевдозаписи - ЧАСТЬ I

Продолжаем наше движение на пути познания триггеров. Одним из интересных моментов при создании строковых триггеров является наличие двух псевдозаписей :old и :new. Строковый триггер срабатывает один раз для каждой строки. При этом внутри триггера можно обращаться к строке обрабатываемой в данный момент времени. Делать это можно как вы уже поняли, применяя псевдозаписи. По своей сути :old и :new вообще-то записями в полном понимании не являются. Например, как рассмотренном нами ранее - таблица%ROWTYPE. Нельзя например, записать: 

CREATE OR REPLACE TRIGGER DLTTSTR
	BEFORE DELETE ON TSTTRIG
	FOR EACH ROW

	m_ROW TSTTRIG%ROWTYPE;
	
DECLARE

BEGIN

	m_ROW = :old;  -- Вызовет ошибку компиляции!

.
.
.

Но при этом верно утверждение что :old и :new это активизирующая_таблица%ROWTYPE, где активизирующая_таблица - это таблица, для которой создан триггер.

Но обращение в нашем случае типа: 

CREATE OR REPLACE TRIGGER DLTTSTR
	BEFORE DELETE ON TSTTRIG
	FOR EACH ROW

	a TSTTRIG.ID%TYPE;
	b TSTTRIG.NM%TYPE;
	c TSTTRIG.ROD%TYPE;

DECLARE

BEGIN

	-- Верно синтаксически!
	a = :old.ID;
	c = :new.ROD;
	b = :old.NM;
.
.
.

Ошибок компиляции не вызовет! Думаю понятно, что обращение к псевдозаписям :old и :new должно производиться через имена полей, по этому они и называются псевдозаписями! Естественно, что все вышесказанное применимо только к строковым триггерам! Обращение к :old и :new в операторных триггерах вызовет ошибку компиляции! Давайте опишем некоторые положения для псевдозаписей :old и :new применимо к операторам DML:

Активизирующий оператор:OLD:NEW
INSERTНе определена во всех полях содержится NULL значенияЗначения, которые будут введены после выполнения оператора.
UPDATEИсходные значения содержащиеся в строке перед обновлением данныхНовые значения которые будут введены после выполнения оператора
DELETEИсходные значения содержащиеся в строке перед ее удалениемНе определена во всех полях содержится NULL значения

Очень хорошо видно, что псевдозапись :old не определена для оператора INSERT, а :new для оператора DELETE! Хотя их применение в триггерах не вызовет ошибки, а значения которые вы получите будут - NULL! Например, есть очень эффективный трюк с заполнением ключевых полей с использованием триггера! Попробуйте выполнить вот такой INSERT для таблицы MILLER.TSTTRIG: 

INSERT INTO TSTTRIG (NM, ROD, INRW)
	VALUES ('BLAKE',  'MANAGER', TO_DATE('8-5-1999', 'DD-MM-YYYY'))
/

Получите примерно вот это: 

SQL7gt; INSERT INTO TSTTRIG (NM, ROD, INRW)
  2    VALUES ('BLAKE',  'MANAGER', TO_DATE('8-5-1999', 'DD-MM-YYYY'))
  3  /
INSERT INTO TSTTRIG (NM, ROD, INRW)
*
ошибка в строке 1:
ORA-01400: невозможно вставить NULL в ("MILLER"."TSTTRIG"."ID")

Естественно нельзя в таблицу добавить запись, не добавляя значение в поле ID, которое является первичным ключом таблицы MILLER.TSTTRIG! Как бы решить эту проблему так, чтобы голова не болела, какой номер следующий в первичном ключе и как бы не ошибиться в следующий раз! А очень просто, применив последовательность и триггер BEFORE INSERT! Сначала создадим последовательность для получения уникальных значений для таблицы MILLER.TSTTRIG. Последовательность напомню - это такой объект БД, который выдает уникальные значения для первичных ключей таблиц БД! Создаем ее: 

CREATE SEQUENCE TRG
	START WITH 8000
	INCREMENT BY 1
/

Начальное значение 8000 и шаг 1-ка: 

SQL> CREATE SEQUENCE TRG
  2   START WITH 8000
  3   INCREMENT BY 1
  4  /

Последовательность создана.

А, вот теперь создаем триггер с применением псевдозаписи :new - вот так: 

CREATE OR REPLACE TRIGGER INSIDTRG
	BEFORE INSERT ON TSTTRIG
	FOR EACH ROW
	
DECLARE

BEGIN
	
	SELECT TRG.NEXTVAL 
		INTO :NEW.ID 
	FROM DUAL;

END INSIDTRG;
/

Получаем: 

SQL> CREATE OR REPLACE TRIGGER INSIDTRG
  2   BEFORE INSERT ON TSTTRIG
  3   FOR EACH ROW
  4  
  5  DECLARE
  6  
  7  BEGIN
  8  
  9   SELECT TRG.NEXTVAL
 10    INTO :NEW.ID
 11   FROM DUAL;
 12  
 13  END INSIDTRG;
 14  /

Триггер создан.

А вот теперь снова попробуем дать наш прошлый INSERT: 

INSERT INTO TSTTRIG (NM, ROD, INRW)
	VALUES ('BLAKE',  'MANAGER', TO_DATE('8-5-1999', 'DD-MM-YYYY'))
/

COMMIT
/

Получаем: 

SQL> INSERT INTO TSTTRIG (NM, ROD, INRW)
  2    VALUES ('BLAKE',  'MANAGER', TO_DATE('8-5-1999', 'DD-MM-YYYY'))
  3  /

1 строка создана.

SQL> COMMIT
  2  /

Фиксация обновлений завершена.

Ух ты! Прокатило! Но как? Ведь поле ID таблицы MILLER.TSTTRIG в нашем случае не получает значения! Да все просто, сработал триггер и отправил значение 8000 в поле ID таблицы MILLER.TSTTRIG при помощи оператора: 

SELECT TRG.NEXTVAL
	INTO :NEW.ID
	FROM DUAL;

В данном случае :NEW.ID вполне справедливо! FROM DUAL - это некая псевдотаблица внутри Oracle для получения отвлеченных значений для SELECT. Ее описание выглядит следующим образом: 

SQL> DESC DUAL
 Имя       Пусто?   Тип
 --------- -------- ----------------------------
 DUMMY              VARCHAR2(1)

На всякий случай посмотрим содержимое таблички MILLER.TSTTRIG: 

SQL> SELECT * FROM TSTTRIG
  2  /

        ID NM       ROD          INRW
---------- -------- ------------ -----------
      7369 SMITH    CLERK        17.02.2000
      7370 JONES    MANAGER      02.04.2001
      7371 MILLER   SALESMAN     20.03.2003
      7372 SCOTT    ANALYST      09.12.2001
      8000 BLAKE    MANAGER      08.05.1999

Вот и вся хитрость! Кстати этот фокус с триггером для заполнения ключевых полей очень пригодиться вам в будущем! Можете пользоваться! А теперь для полного понимания попробуем изменить триггеры из прошлого шага и рассмотреть псевдозаписи :old и :new более детально! Итак, создадим и изменим триггера: 

CREATE OR REPLACE TRIGGER DLTTSTR
	BEFORE DELETE ON TSTTRIG
	FOR EACH ROW
	
DECLARE

BEGIN

INSERT INTO ADT(USAL, TISP, WDO, PRIM)
	VALUES(USER, SYSDATE, NULL, 'DELETE '||:old.ROD);

END BFOTSTR;
/

CREATE OR REPLACE TRIGGER BFOTSTR
	BEFORE UPDATE ON TSTTRIG
	FOR EACH ROW
	
DECLARE

BEGIN

INSERT INTO ADT(USAL, TISP, WDO, PRIM)
	VALUES(USER, SYSDATE, :OLD.ROD, 'UPDATE TO '||:new.ROD);

END BFOTSTR;
/


CREATE OR REPLACE TRIGGER AFTTSTR
	AFTER UPDATE ON TSTTRIG
	FOR EACH ROW
	
DECLARE

BEGIN

INSERT INTO ADT(USAL, TISP, WDO, PRIM)
	VALUES(USER, SYSDATE, :new.ROD, :old.ROD);

END AFTTSTR;
/

Получаем: 

SQL> CREATE OR REPLACE TRIGGER DLTTSTR
  2   BEFORE DELETE ON TSTTRIG
  3   FOR EACH ROW
  4  
  5  DECLARE
  6  
  7  BEGIN
  8  
  9  INSERT INTO ADT(USAL, TISP, WDO, PRIM)
 10    VALUES(USER, SYSDATE, NULL, 'DELETE '||:old.ROD);
 11  
 12  END BFOTSTR;
 13  /

Триггер создан.

SQL> CREATE OR REPLACE TRIGGER BFOTSTR
  2   BEFORE UPDATE ON TSTTRIG
  3   FOR EACH ROW
  4  
  5  DECLARE
  6  
  7  BEGIN
  8  
  9  INSERT INTO ADT(USAL, TISP, WDO, PRIM)
 10    VALUES(USER, SYSDATE, :OLD.ROD, 'UPDATE TO '||:new.ROD);
 11  
 12  END BFOTSTR;
 13  /

Триггер создан.

SQL> CREATE OR REPLACE TRIGGER AFTTSTR
  2   AFTER UPDATE ON TSTTRIG
  3   FOR EACH ROW
  4  
  5  DECLARE
  6  
  7  BEGIN
  8  
  9  INSERT INTO ADT(USAL, TISP, WDO, PRIM)
 10    VALUES(USER, SYSDATE, :new.ROD, :old.ROD);
 11  
 12  END AFTTSTR;
 13  /

Триггер создан.

Триггер DLTTSTR срабатывает на удаление строки из таблицы MILLER.TSTTRIG, а два триггера BFOTSTR, AFTTSTR из прошлого шага теперь вставляют в таблицу MILLER.ADT старые (:old) и новые (:new) значения для полей активизирующей таблицы. Очистим таблицу MILLER.ADT для чистоты эксперимента: 

DELETE FROM MILLER.ADT
/

COMMIT
/

Снова изменим, значения полей таблички MILLER.TSTTRIG: 

UPDATE MILLER.TSTTRIG
SET ROD = 'SPOOKY'
WHERE ID IN (7369, 7370)
/

COMMIT
/

Получаем: 

SQL> UPDATE MILLER.TSTTRIG
  2  SET ROD = 'SPOOKY'
  3  WHERE ID IN (7369, 7370)
  4  /

2 строк обновлено.

SQL> COMMIT
  2  /

Фиксация обновлений завершена.

Смотрим содержимое - MILLER.ADT: 

SQL> SELECT WDO, PRIM FROM MILLER.ADT
  2  /

WDO           PRIM
------------- -------------------------
Update        Before Statement trigger
MANAGER       UPDATE TO SPOOKY
SPOOKY        MANAGER
CLERK         UPDATE TO SPOOKY
SPOOKY        CLERK
Update        After Statement trigger

6 строк выбрано.

Видно как сработал операторный триггер первая и последняя строки и строковые триггеры, показали :old и :new значения полей для нашей таблички MILLER.TSTTRIG. Думаю теперь ясно, как работать с псевдозаписями :old и :new! Думаю пришла пора заставить их делать что-то более полезное. Давайте, реализуем бизнес - правило, одно из ключевых применений строчных триггеров. И кое что еще! Недаром вначале я делал две таблицы и связывал их! Давайте для начала удалим связь MILLER.TSTTRIG и MILLER.TSTSV для этого нужно узнать ее имя, как это делать смотрите "Шаг 100 - PL/SQL - Уровни строгости - Прагма RESTRICT_REFERENCES". В моем случае она зовется SYS_C003552 удалим ее: 

SQL> ALTER TABLE MILLER.TSTSV DROP CONSTRAINT SYS_C003552
  2  /

Таблица изменена.

Теперь будем писать осмысленные триггеры реализующие бизнес правила для таблиц MILLER.TSTTRIG и MILLER.TSTSV! Сначала удалим все наши учебные триггеры больше они нам не понадобятся (оставим только INSIDTRG он нам нужен): 

SQL> DROP TRIGGER AFTTST
  2  /

Триггер удален.

SQL> DROP TRIGGER AFTTSTR
  2  /

Триггер удален.

SQL> DROP TRIGGER BFOTST 
  2  /

Триггер удален.

SQL> DROP TRIGGER BFOTSTR
  2  /

Триггер удален.

SQL> DROP TRIGGER DLTTSTR
  2  /

Триггер удален.

Все, от мусора избавились, пора заняться делом! Чтобы не превышать лимит по объему материала продолжим в следующем шаге! Не уходите далеко!

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