Шаг 116 - PL/SQL - Триггеры - ЧАСТЬ IV - предикаты

Продолжаем работу с триггерами и всем, что с ними связано! В триггерах БД Oracle возможно применение логических операторов - так называемых предикатов. Они имеют следующие определения INSERTING, UPDATING, DELETING. Это некие внутренние переменные среды Oracle, которые в зависимости от воздействующего на таблицу оператора DML принимают одно из значений TRUE или FALSE. С их помощью можно значительно сэкономить при написании кода, в чем вы в дальнейшем убедитесь и не плодить слишком большое количество объектов БД.

Кратко их можно описать вот так:

ПредикатПринимаемое значение
INSERTINGTRUE если, активизирующий оператор INSERT. FALSE в противном случае.
UPDATINGTRUE если, активизирующий оператор UPDATE. FALSE в противном случае.
DELETINGTRUE если, активизирующий оператор DELETE. FALSE в противном случае.

Для полноты понимания давайте все рассмотрим на практическом примере! Так как это самый эффективный способ что-либо запомнить! Итак, создадим некий аудит нашей таблички из прошлого шага TSTTRIG. Создадим таблицу вида: 

CREATE TABLE MYAUDIT
(
	POLZ VARCHAR2(15),
	VIZM DATE,
	OPER VARCHAR2(20),
	NZAP NUMBER,
	HIST VARCHAR2(50)
)
/

Получаем: 

SQL> CREATE TABLE MYAUDIT
  2  (
  3   POLZ VARCHAR2(15),
  4   VIZM DATE,
  5   OPER VARCHAR2(20),
  6   NZAP NUMBER,
  7   HIST VARCHAR2(50)
  8  )
  9  /

Таблица создана.

В ней мы будем хранить данные, которые будут меняться во время нашего примера. Далее давайте применим на практике предикаты INSERTING, UPDATING, DELETING - для написания одного, но очень эффективного триггера вот такого вида: 

CREATE OR REPLACE TRIGGER AUDT_TSTTRIG
	BEFORE INSERT OR UPDATE OR DELETE ON TSTTRIG
	FOR EACH ROW
	
DECLARE

TIP VARCHAR2(10);

BEGIN
	
	IF    INSERTING THEN
		  TIP := 'INSERT';
	ELSIF UPDATING THEN
		  TIP := 'UPDATE';
	ELSIF DELETING THEN
		  TIP := 'DELETE';
	END IF;
	
	INSERT INTO MYAUDIT(MYAUDIT.POLZ, MYAUDIT.VIZM, MYAUDIT.OPER, MYAUDIT.NZAP, MYAUDIT.HIST)
		VALUES (USER, SYSDATE, TIP, :new.ID, 'Old Name: '||:old.NM||' New Name: '||:new.NM);
	
END AUDT_TSTTRIG;
/

Получаем после компиляции: 

SQL> CREATE OR REPLACE TRIGGER AUDT_TSTTRIG
  2   BEFORE INSERT OR UPDATE OR DELETE ON TSTTRIG
  3   FOR EACH ROW
  4  
  5  DECLARE
  6  
  7  TIP VARCHAR2(10);
  8  
  9  BEGIN
 10  
 11   IF    INSERTING THEN
 12      TIP := 'INSERT';
 13   ELSIF UPDATING THEN
 14      TIP := 'UPDATE';
 15   ELSIF DELETING THEN
 16      TIP := 'DELETE';
 17   END IF;
 18  
 19   INSERT INTO MYAUDIT(MYAUDIT.POLZ, MYAUDIT.VIZM, MYAUDIT.OPER, MYAUDIT.NZAP, MYAUDIT.HIST)
 20    VALUES (USER, SYSDATE, TIP, :new.ID, 'Old Name: '||:old.NM||' New Name: '||:new.NM);
 21  
 22  END AUDT_TSTTRIG;
 23  /

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

Данный триггер имеет временное действие "ДО"! Попробуем добавить запись в таблицу TSTTRIG вот так: 

INSERT INTO TSTTRIG (NM, ROD, INRW, COST)
		VALUES ('ALFRED',  'MANAGER', TO_DATE('18-12-2002', 'DD-MM-YYYY'), 40967)
/

COMMIT
/

Получаем: 

SQL> INSERT INTO TSTTRIG (NM, ROD, INRW, COST)
  2    VALUES ('ALFRED',  'MANAGER', TO_DATE('18-12-2002', 'DD-MM-YYYY'), 40967)
  3  /

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

SQL> COMMIT
  2  /

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

Смотрим содержимое таблицы MYAUDIT применив запрос вида: 

SQL> SELECT * FROM MYAUDIT
  2  /

POLZ     VIZM        OPER     NZAP     HIST
-------- ----------- -------- -------- -----------------------------------------
MILLER   20.03.2004  INSERT   NULL     Old Name: "NULL" New Name: ALFRED

Здесь в строке Old Name: "NULL" New Name: ALFRED я поставил "NULL" чисто фигурально, чтобы было понятно. Сразу попутно запоминайте, что псевдозапись :old для DML оператора INSERT триггера типа BEFORE не определена! А вот псевдозапись :new для поля ID, так же еще не получила значения! Почему можете подумать сами! Вспомните для начала как оно вообще формируется? Далее поле OPER после сработки условия .. IF INSERTING THEN .. получаем, что была операция INSERT! Что собственно хорошо видно! Теперь давайте, попробуем изменить запись вот так: 

UPDATE TSTTRIG 
	SET NM = 'ALF'
WHERE NM = 'ALFRED'
/

COMMIT
/

Получаем: 

SQL> UPDATE TSTTRIG
  2   SET NM = 'ALF'
  3  WHERE NM = 'ALFRED'
  4  /

1 строка обновлена.

SQL> COMMIT
  2  /

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

Альфред стал инопланетянином Альфом! Помните такого? Не важно! А, важно вот что: 

SQL> SELECT * FROM MYAUDIT
  2  /

POLZ     VIZM        OPER     NZAP     HIST
-------- ----------- -------- -------- -----------------------------------------
MILLER   20.03.2004  INSERT   NULL     Old Name: "NULL" New Name: ALFRED
MILLER   20.03.2004  UPDATE   8001     Old Name: ALFRED New Name: ALF

Здесь строка Old Name: ALFRED New Name: ALF показывает, что псевдозаписи :new и :old применительно для оператора UPDATE, для триггера типа BEFORE определены! Кстати псевдозаписи :new так же можно изменить!!! Запоминайте! Здесь для поля NZAP получаем 8001, все верно! В данном случае в поле OPER после сработки условия .. IF INSERTING THEN .. получаем, что была операция UPDATE! Что собственно хорошо видно! Теперь давайте, попробуем удалить запись вот так: 

DELETE FROM TSTTRIG
WHERE NM = 'ALF'
/

COMMIT
/

Получаем: 

SQL> DELETE FROM TSTTRIG
  2  WHERE NM = 'ALF'
  3  /

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

SQL> COMMIT
  2  /

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

Смотрим содержимое таблицы аудита: 

SQL> SELECT * FROM MYAUDIT
  2  /

POLZ     VIZM        OPER     NZAP     HIST
-------- ----------- -------- -------- -----------------------------------------
MILLER   20.03.2004  INSERT   NULL     Old Name: "NULL" New Name: ALFRED
MILLER   20.03.2004  UPDATE   8001     Old Name: ALFRED New Name: ALF
MILLER   20.03.2004  DELETE   NULL     Old Name: ALF New Name: "NULL"

Здесь строка Old Name: ALF New Name: "NULL" показывает, что псевдозаписи :new применительно для оператора DELETE, для триггера типа BEFORE не определена! Запоминайте! Здесь, для поля NZAP получаем NULL, это вам так же к слову подумать почему! В данном случае в поле OPER после сработки условия .. IF INSERTING THEN .. получаем, что была операция DELETE! Что собственно хорошо видно! На этом можно было бы поставить точку, но давайте проделаем еще кое-что! Создадим, вот такой триггер: 

CREATE OR REPLACE TRIGGER AFT_AUDT_TSTTRIG
	AFTER INSERT OR UPDATE OR DELETE ON TSTTRIG
	FOR EACH ROW
	
DECLARE

TIP VARCHAR2(10);

BEGIN
	
	IF    INSERTING THEN
		  TIP := 'INSERT';
	ELSIF UPDATING THEN
		  TIP := 'UPDATE';
	ELSIF DELETING THEN
		  TIP := 'DELETE';
	END IF;
	
	INSERT INTO MYAUDIT(MYAUDIT.POLZ, MYAUDIT.VIZM, MYAUDIT.OPER, MYAUDIT.NZAP, MYAUDIT.HIST)
		VALUES (USER, SYSDATE, TIP, :new.ID, 'Old Name: '||:old.NM||' New Name: '||:new.NM);
	
END AFT_AUDT_TSTTRIG;
/

Как видно я поменял его имя и сменил контент времени на AFTER! Давайте проделаем все еще раз, но при этом триггер AUDT_TSTTRIG заблокируем вот такой командой: 

ALTER TRIGGER AUDT_TSTTRIG DISABLE
/

Получаем: 

SQL> ALTER TRIGGER AUDT_TSTTRIG DISABLE
  2  /

Триггер изменен.

А, так же давайте очистим нашу табличку аудита от старых данных: 

DELETE FROM MYAUDIT
/

Видим: 

SQL> DELETE FROM MYAUDIT
  2  /

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

SQL> COMMIT
  2  /

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

Теперь давайте проделаем все сначала. Снова добавляем запись: 

INSERT INTO TSTTRIG (NM, ROD, INRW, COST)
		VALUES ('ALFRED',  'MANAGER', TO_DATE('18-12-2002', 'DD-MM-YYYY'), 40967)
/

COMMIT
/

Получаем: 

SQL> INSERT INTO TSTTRIG (NM, ROD, INRW, COST)
  2    VALUES ('ALFRED',  'MANAGER', TO_DATE('18-12-2002', 'DD-MM-YYYY'), 40967)
  3  /

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

SQL> COMMIT
  2  /

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

Смотрим, что получилось в таблице MYAUDIT: 

SELECT * FROM MYAUDIT
/

Видим: 

SQL> SELECT * FROM MYAUDIT
  2  /

POLZ     VIZM        OPER     NZAP     HIST
-------- ----------- -------- -------- -----------------------------------------
MILLER   20.03.2004  INSERT   8003     Old Name: "NULL" New Name: ALFRED

Все почти так же, но теперь псевдозапись :new для поля ID уже получила значение и его видно! Остальное не изменилось в части действия триггера. Изменим запись: 

UPDATE TSTTRIG 
	SET NM = 'ALF'
WHERE NM = 'ALFRED'
/

COMMIT
/

Получим: 

SQL> UPDATE TSTTRIG
  2   SET NM = 'ALF'
  3  WHERE NM = 'ALFRED'
  4  /

1 строка обновлена.

SQL> COMMIT
  2  /

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

Снова смотрим табличку MYAUDIT: 

SQL> SELECT * FROM MYAUDIT
  2  /

POLZ     VIZM        OPER     NZAP     HIST
-------- ----------- -------- -------- -----------------------------------------
MILLER   20.03.2004  INSERT   8003     Old Name: "NULL" New Name: ALFRED
MILLER   20.03.2004  UPDATE   8003     Old Name: ALFRED New Name: ALF

Здесь все по старому, как и в прошлый раз. Удалим запись: 

DELETE FROM TSTTRIG
WHERE NM = 'ALF'
/

COMMIT
/

Получаем: 

SQL> DELETE FROM TSTTRIG
  2  WHERE NM = 'ALF'
  3  /

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

SQL> COMMIT
  2  /

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

Снова смотрим табличку MYAUDIT: 

SQL> SELECT * FROM MYAUDIT
  2  /

POLZ     VIZM        OPER     NZAP     HIST
-------- ----------- -------- -------- -----------------------------------------
MILLER   20.03.2004  INSERT   8003     Old Name: "NULL" New Name: ALFRED
MILLER   20.03.2004  UPDATE   8003     Old Name: ALFRED New Name: ALF
MILLER   20.03.2004  DELETE   NULL     Old Name: ALF New Name: "NULL"

Здесь все, так же как и прошлый раз. Теперь я думаю, вы не запутаетесь в трех соснах AFTER, BEFORE, new, old! Что и когда нужно использовать и как! В "Шаг 111 - PL/SQL - Триггеры таблиц БД, операторный триггер" я не указал как активировать триггер после его деактивации! Собственно почти так же! Вот такой командой: 

ALTER TRIGGER AUDT_TSTTRIG ENABLE
/

Триггер AUDT_TSTTRIG снова активен: 

SQL> ALTER TRIGGER AUDT_TSTTRIG ENABLE
  2  /

Триггер изменен.

А вот теперь давайте добавим еще запись - вот так: 

INSERT INTO TSTTRIG (NM, ROD, INRW, COST)
		VALUES ('MALKOVISH',  'ACTORS', TO_DATE('18-12-2002', 'DD-MM-YYYY'), 40967)
/

COMMIT
/

Получаем: 

SQL> INSERT INTO TSTTRIG (NM, ROD, INRW, COST)
  2    VALUES ('MALKOVISH',  'ACTORS', TO_DATE('18-12-2002', 'DD-MM-YYYY'), 40967)
  3  /

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

SQL> COMMIT
  2  /

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

А, вот теперь посмотрите на табличку MYAUDIT: 

SQL> SELECT * FROM MYAUDIT
  2  /

POLZ     VIZM        OPER     NZAP     HIST
-------- ----------- -------- -------- -----------------------------------------
MILLER   20.03.2004  INSERT            Old Name: "NULL" New Name: MALKOVISH
MILLER   20.03.2004  INSERT   8004     Old Name: "NULL" New Name: MALKOVISH
MILLER   20.03.2004  DELETE            Old Name: ALF New Name: "NULL"
MILLER   20.03.2004  INSERT   8003     Old Name: "NULL" New Name: ALFRED
MILLER   20.03.2004  UPDATE   8003     Old Name: ALFRED New Name: ALF

Хорошо видно как оба триггера отработали! И именно так как мы рассматривали с вами ранее! По сути, эти два триггера заменяют шесть (!) вот вам экономия кода! Хотя иногда не мешает поработать ручками! Советую все это прочесть еще раз и запомнить! Удачи! :)

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