Продолжаем наше движение на пути познания триггеров. Одним из интересных моментов при создании строковых триггеров является наличие двух псевдозаписей :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 / Триггер удален.
Все, от мусора избавились, пора заняться делом! Чтобы не превышать лимит по объему материала продолжим в следующем шаге! Не уходите далеко!