Шаг 114 - PL/SQL - Триггеры - псевдозаписи - ЧАСТЬ II (бизнес правила)

На чем мы там остановились? Да! Бизнес правила. Итак, давайте создадим некий костяк, который будет определять взаимоотношения наших двух таблиц. Начнем с добавления записи в таблицу MILLER.TSTTRIG. Предположим, что каждое добавление записи должно отражаться сразу в двух таблицах БД. Как это сделать? Написать триггер БД, который реализует данное правило. Для работы нам понадобиться еще одна последовательность, для таблицы MILLER.TSTSV - создадим ее:

CREATE SEQUENCE SV
	START WITH 5
	INCREMENT BY 1
/

Получаем:

SQL> CREATE SEQUENCE SV
  2   START WITH 5
  3   INCREMENT BY 1
  4  /

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

Теперь создадим триггер для таблицы MILLER.TSTTRIG, который одновременно будет менять содержимое таблицы - MILLER.TSTSV реализуя наше бизнес правило для MILLER.TSTTRIG. Создаем:

CREATE OR REPLACE TRIGGER AFTINSTTRIG
	AFTER INSERT ON TSTTRIG
	FOR EACH ROW
	
DECLARE

BEGIN

INSERT INTO MILLER.TSTSV(MILLER.TSTSV.ID, MILLER.TSTSV.IDD, MILLER.TSTSV.ROD, MILLER.TSTSV.CONS)
	VALUES(SV.NEXTVAL, :NEW.NM, :NEW.ROD, :NEW.ID);

END AFTINSTTRIG;
/

Получаем:

SQL> CREATE OR REPLACE TRIGGER AFTINSTTRIG
  2   AFTER INSERT ON TSTTRIG
  3   FOR EACH ROW
  4   
  5  DECLARE
  6  
  7  BEGIN
  8  
  9  INSERT INTO MILLER.TSTSV(MILLER.TSTSV.ID, MILLER.TSTSV.IDD, MILLER.TSTSV.ROD, MILLER.TSTSV.CONS
)
 10    VALUES(SV.NEXTVAL, :NEW.NM, :NEW.ROD, :NEW.ID);
 11  
 12  END AFTINSTTRIG;
 13  /

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

Триггер срабатывает после вставки в таблицу MILLER.TSTTRIG и так же добавляет данные согласно нашему бизнес правилу! Проверим его работу. Удалите запись в таблице MILLER.TSTTRIG с ID = 8000! (или с другим, если вы уже что-то меняли!):

SQL> DELETE FROM TSTTRIG
  2  WHERE ID = 8000
  3  /

1 строка удалена.

Вот так. А теперь возьмем оператор из прошлого шага - помните:

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  /
  
Фиксация обновлений завершена.

Смотрим содержимое таблиц MILLER.TSTTRIG и MILLER.TSTSV:

SELECT * FROM MILLER.TSTTRIG
/

SELECT * FROM MILLER.TSTSV
/

Получаем:

SQL> SELECT * FROM MILLER.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
      8001 BLAKE	MANAGER      08.05.1999
      
SQL> SELECT * FROM MILLER.TSTSV
  2  /

        ID IDD      ROD         CONS
---------- -------- ----------- ----------
         1 SMITH    CLERK       7369
         2 JONES    MANAGER     7370
         3 MILLER   SALESMAN    7371
         4 SCOTT    ANALYST     7372
		 5 BLAKE	MANAGER     8001

Первый триггер сработал верно, тоже самое будет, если вы добавите еще запись! Таким образом один INSERT работает на две таблицы. Триггер типа AFRTER т.к. поле :NEW.ID определяется после вставки! Думаю все догадались, что здесь сработало два триггера (!) второй определил поле ID в MILLER.TSTTRIG! А вы даже об этом не задумались! Удобно верно? :) Идем дальше - обновление для пары таблиц:

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

BEGIN

UPDATE MILLER.TSTSV
SET 
	MILLER.TSTSV.IDD = :NEW.NM, 
	MILLER.TSTSV.ROD = :NEW.ROD, 
	MILLER.TSTSV.CONS = :NEW.ID
	
WHERE 	
	MILLER.TSTSV.CONS = :OLD.ID; 
	
END AFTUPDTTRIG;
/

Получаем:

SQL> CREATE OR REPLACE TRIGGER AFTUPDTTRIG
  2   AFTER UPDATE ON TSTTRIG
  3   FOR EACH ROW
  4   
  5  DECLARE
  6  
  7  BEGIN
  8  
  9  UPDATE MILLER.TSTSV
 10  SET 
 11   MILLER.TSTSV.IDD = :NEW.NM, 
 12   MILLER.TSTSV.ROD = :NEW.ROD, 
 13   MILLER.TSTSV.CONS = :NEW.ID
 14   
 15  WHERE  
 16   MILLER.TSTSV.CONS = :OLD.ID; 
 17   
 18  END AFTUPDTTRIG;
 19  /
 
Триггер создан.

Данный триггер следит за изменением полей NM, ROD, ID в таблице MILLER.TSTTRIG и отражает изменения в таблице MILLER.TSTSV. Например, попробуйте вот этот оператор:

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.TSTTRIG и MILLER.TSTSV:

SELECT * FROM MILLER.TSTTRIG
/

SELECT * FROM MILLER.TSTSV
/

Получаем:

SQL> SELECT * FROM MILLER.TSTTRIG
  2  /

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

SQL> SELECT * FROM MILLER.TSTSV
  2  /

        ID IDD      ROD         CONS
---------- -------- ----------- ----------
         1 SMITH    SPOOKY      7369
         2 JONES    SPOOKY      7370
         3 MILLER   SALESMAN    7371
         4 SCOTT    ANALYST     7372
		 5 BLAKE	MANAGER     8001

Изменения отражены в обеих таблицах! Что и должно было произойти при реализации этого бизнес правила! Один UPDATE отразился на обоих таблицах. Можете сами проверить действие изменений поля CONS зависящее от поля ID в этих двух таблицах. Ну и последнее удаление данных. Запишем такой триггер:

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

BEGIN

DELETE FROM MILLER.TSTSV
	WHERE MILLER.TSTSV.CONS = :OLD.ID; 
	
END BFRDELTTRIG;
/

Получаем:

SQL> CREATE OR REPLACE TRIGGER BFRDELTTRIG
  2   BEFORE DELETE ON TSTTRIG
  3   FOR EACH ROW
  4  
  5  DECLARE
  6  
  7  BEGIN
  8  
  9  DELETE FROM MILLER.TSTSV
 10    WHERE MILLER.TSTSV.CONS = :OLD.ID;
 11  
 12  END BFRDELTTRIG;
 13  /

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

Здесь используется BEFORE, то есть "перед тем как убери за собой"! :) Смотрим как он работает. Удалим запись с номером 8001 в таблице MILLER.TSTTRIG (или другой, если вы уже изменяли ее) вот так:

SQL> DELETE FROM TSTTRIG
  2  WHERE ID = 8001
  3  /

1 строка удалена.

SQL> COMMIT
  2  /

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

Смотрим таблички MILLER.TSTTRIG и MILLER.TSTSV:

SELECT * FROM MILLER.TSTTRIG
/

SELECT * FROM MILLER.TSTSV
/

Получаем:

SQL> SELECT * FROM MILLER.TSTTRIG
  2  /

        ID NM       ROD          INRW
---------- -------- ------------ -----------
      7369 SMITH    SPOOKY       17.02.2000
      7370 JONES    SPOOKY       02.04.2001
      7371 MILLER   SALESMAN     20.03.2003
      7372 SCOTT    ANALYST      09.12.2001   

SQL> SELECT * FROM MILLER.TSTSV
  2  /

        ID IDD      ROD         CONS
---------- -------- ----------- ----------
         1 SMITH    SPOOKY      7369
         2 JONES    SPOOKY      7370
         3 MILLER   SALESMAN    7371
         4 SCOTT    ANALYST     7372

Все вернулось на исходную. А триггер BFRDELTTRIG провел что-то вроде каскадного удаления. Вот таким образом можно построить, достаточно сложные взаимосвязи таблиц друг с другом. Обеспечить некую правовую политику, для вашей БД! Хотя мой пример достаточно прост думаю для вас теперь более понятно, как строить бизнес правила для таблиц БД. К слову скажу, что менять псевдозапись :new в строковом триггере AFTER не имеет смысла, так как событие уже обработано. Менять псевдозапись :new возможно в строковом триггере BEFORE! А вот псевдозапись :old никогда не модифицируется, а только считывается. Для закрепления можете сами создать несколько таблиц в вашей схеме и связать их при помощи триггеров. При этом будьте внимательнее при использовании событий BEFORE и AFTER! Удачи! :)


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