Шаг 115 - PL/SQL - Триггеры - ЧАСТЬ III - условие WHERE

Как ни странно, но триггеры довольно объемный материал! Продолжаем. Давайте рассмотрим такой момент создания триггера БД, кода в нем применяется условие 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". Пробуйте! :)


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