Как ни странно, но триггеры довольно объемный материал! Продолжаем. Давайте рассмотрим такой момент создания триггера БД, кода в нем применяется условие WHERE! С его помощью можно заставить триггер работать - по условию! Само условие WHERE в триггере применимо к типу строчных триггеров. Давайте, рассмотрим, как это реализуется на практике. Но предварительно добавим по одному полю в наши таблички из прошлых шагов - вот так:
ALTER TABLE TSTTRIG ADD COST NUMBER / ALTER TABLE TSTSV ADD ITOG NUMBER /
Получаем:
SQL> ALTER TABLE TSTTRIG ADD COST NUMBER 2 / Таблица изменена. SQL> ALTER TABLE TSTSV ADD ITOG NUMBER 2 / Таблица изменена.
В этих полях мы будем хранить наши данные для последующего использования. Далее давайте добавим данные пока только в таблицу TSTTRIG, с помощью операторов UPDATE вот так:
UPDATE TSTTRIG SET COST = 30532 WHERE NM = 'SMITH' / UPDATE TSTTRIG SET COST = 80478 WHERE NM = 'JONES' / UPDATE TSTTRIG SET COST = 20785 WHERE NM = 'MILLER' / UPDATE TSTTRIG SET COST = 10205 WHERE NM = 'SCOTT' / COMMIT /
Получаем:
SQL> UPDATE TSTTRIG 2 SET COST = 30532 3 WHERE NM = 'SMITH' 4 / 1 строка обновлена. SQL> UPDATE TSTTRIG 2 SET COST = 80478 3 WHERE NM = 'JONES' 4 / 1 строка обновлена. SQL> UPDATE TSTTRIG 2 SET COST = 20785 3 WHERE NM = 'MILLER' 4 / 1 строка обновлена. SQL> UPDATE TSTTRIG 2 SET COST = 10205 3 WHERE NM = 'SCOTT' 4 / 1 строка обновлена. SQL> COMMIT 2 / Фиксация обновлений завершена.
Итак, поле и данные в них добавлены. Давайте посмотрим, как создавать триггер с условием. Запишем вот такую конструкцию:
CREATE OR REPLACE TRIGGER WHENTRG BEFORE INSERT OR UPDATE OF COST ON TSTTRIG FOR EACH ROW WHEN (new.COST > 10000) DECLARE BEGIN UPDATE TSTSV SET TSTSV.ITOG = :new.COST + :old.COST WHERE TSTSV.CONS = :old.ID; END WHENTRG; /
Обратите внимание на наличие строки OF COST ON TSTTRIG - здесь определяется поле, на которое устанавливаем условие триггера и собственно само условие WHEN (new.COST > 10000) - обратите внимание, что псевдозапись new записана как - new, а не :new ! Это важно! В условии псевдозаписи записываются БЕЗ ДВОЕТОЧИЯ! Запомните! Двоеточие применяется только в теле триггера! Условие можно строить и по другому, так как нужно вам. Данный триггер производит довольно глупое действие, но зато наглядное! При вставке или изменении, он просто запоминает сумму чисел в поле ITOG таблички TSTSV! Это просто пример, но с его помощью вы сможете разобраться как это все работает! Итак, SQL*Plus и компилируем:
SQL> CREATE OR REPLACE TRIGGER WHENTRG 2 BEFORE INSERT OR UPDATE OF COST ON TSTTRIG 3 FOR EACH ROW 4 WHEN (new.COST > 10000) 5 6 DECLARE 7 8 BEGIN 9 10 UPDATE TSTSV 11 SET TSTSV.ITOG = :new.COST + :old.COST 12 WHERE TSTSV.CONS = :old.ID; 13 14 END WHENTRG; 15 / Триггер создан.
Прекрасно! Теперь поработаем с ним. Давайте теперь изменим одну из записей таблички TSTTRIG (запомнив, что ее старое содержимое было равно 10205) вот так:
UPDATE TSTTRIG SET COST = 20205 WHERE NM = 'SCOTT' / COMMIT /
Получаем:
SQL> UPDATE TSTTRIG 2 SET COST = 20205 3 WHERE NM = 'SCOTT' 4 / 1 строка обновлена. SQL> COMMIT 2 / Фиксация обновлений завершена.
А, вот теперь давайте посмотрим, что изменилось в полях наших с вами таблиц:
SELECT * FROM MILLER.TSTTRIG / SELECT * FROM MILLER.TSTSV /
Видим:
SQL> SELECT * FROM TSTTRIG
2 /
ID NM ROD INRW COST
---------- -------- ------------ ----------- ----------
7369 SMITH CLERK 17.02.2000 30532
7370 JONES MANAGER 02.04.2001 80478
7371 MILLER SALESMAN 20.03.2003 20785
7372 SCOTT ANALYST 09.12.2001 20205
SQL> SELECT * FROM MILLER.TSTSV
2 /
ID IDD ROD CONS ITOG
---------- -------- ----------- ---------- ---------
1 SMITH CLERK 7369 NULL
2 JONES MANAGER 7370 NULL
3 MILLER SALESMAN 7371 NULL
4 SCOTT ANALYST 7372 30410
Если применить элементарные знания математики за первый класс, то 10205 + 20205 = 30410. Все верно! Как видим условие дало TRUE и триггер сработал. А что, если изменить запись MILLER-а вот так:
UPDATE TSTTRIG SET COST = 5342 WHERE NM = 'MILLER' / COMMIT /
Видим:
SQL> UPDATE TSTTRIG 2 SET COST = 5342 3 WHERE NM = 'MILLER' 4 / 1 строка обновлена. SQL> COMMIT 2 / Фиксация обновлений завершена.
А теперь давайте посмотрим, что содержат таблицы после изменения:
SQL> SELECT * FROM TSTTRIG
2 /
ID NM ROD INRW COST
---------- -------- ------------ ----------- ----------
7369 SMITH CLERK 17.02.2000 30532
7370 JONES MANAGER 02.04.2001 80478
7371 MILLER SALESMAN 20.03.2003 20785
7372 SCOTT ANALYST 09.12.2001 20205
SQL> SELECT * FROM MILLER.TSTSV
2 /
ID IDD ROD CONS ITOG
---------- -------- ----------- ---------- ---------
1 SMITH CLERK 7369 NULL
2 JONES MANAGER 7370 NULL
3 MILLER SALESMAN 7371 NULL
4 SCOTT ANALYST 7372 30410
То же самое! Верно, ведь если снова вспомнить первый класс, то 5342 < 10000. Что дает в условии WHERE триггера FALSE и он не срабатывает! Что собственно и ожидалось! А что, если запись не изменять, а добавить? Что тогда будет происходить? Давайте посмотрим. Добавим вот такую запись:
INSERT INTO TSTTRIG (NM, ROD, INRW, COST)
VALUES ('BOB', 'DUMMY', TO_DATE('9-11-1989', 'DD-MM-YYYY'), 24734)
/
COMMIT
/
Получаем (помните? С прошлых шагов, все ранее созданные триггера уже сделали свое дело!):
SQL> INSERT INTO TSTTRIG (NM, ROD, INRW, COST)
2 VALUES ('BOB', 'DUMMY', TO_DATE('9-11-1989', 'DD-MM-YYYY'), 24734)
3 /
1 строка создана.
SQL> COMMIT
2 /
Фиксация обновлений завершена.
Смотрим содержимое табличек после добавления записи:
SQL> SELECT * FROM TSTTRIG
2 /
ID NM ROD INRW COST
---------- -------- ------------ ----------- ----------
8000 BOB DUMMY 09.11.1989 24734
7369 SMITH CLERK 17.02.2000 30532
7370 JONES MANAGER 02.04.2001 80478
7371 MILLER SALESMAN 20.03.2003 20785
7372 SCOTT ANALYST 09.12.2001 20205
SQL> SELECT * FROM MILLER.TSTSV
2 /
ID IDD ROD CONS ITOG
---------- -------- ----------- ---------- ---------
5 BOB DUMMY 8000 NULL
1 SMITH CLERK 7369 NULL
2 JONES MANAGER 7370 NULL
3 MILLER SALESMAN 7371 NULL
4 SCOTT ANALYST 7372 30410
Как видим запись (8000, BOB, DUMMY, 09.11.1989, 24734) - добавлена в таблицу TSTTRIG. Триггеры из прошлых шагов так же добавили запись - (5, BOB, DUMMY, 8000, NULL). Но вот почему поле ITOG таблицы TSTSV содержит NULL? Странно, не правда ли? Ведь условие 24734 > 10000 дает TRUE! Да, триггер WHENTRG свою работу выполнил! Но дело в том, что псевдозапись :new для триггеров по INSERT не определена, то есть содержит - NULL! Отсюда NULL + 24734 = NULL! Что собственно и ожидалось! Вспоминаем тройственную логику, если кто забыл - "Шаг 17 - Составные операторы в условии WHERE". Пробуйте! :)