Продолжаем работу с триггерами и всем, что с ними связано! В триггерах БД Oracle возможно применение логических операторов - так называемых предикатов. Они имеют следующие определения INSERTING, UPDATING, DELETING. Это некие внутренние переменные среды Oracle, которые в зависимости от воздействующего на таблицу оператора DML принимают одно из значений TRUE или FALSE. С их помощью можно значительно сэкономить при написании кода, в чем вы в дальнейшем убедитесь и не плодить слишком большое количество объектов БД.
Кратко их можно описать вот так:
Предикат | Принимаемое значение |
---|---|
INSERTING | TRUE если, активизирующий оператор INSERT. FALSE в противном случае. |
UPDATING | TRUE если, активизирующий оператор UPDATE. FALSE в противном случае. |
DELETING | TRUE если, активизирующий оператор 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
Хорошо видно как оба триггера отработали! И именно так как мы рассматривали с вами ранее! По сути, эти два триггера заменяют шесть (!) вот вам экономия кода! Хотя иногда не мешает поработать ручками! Советую все это прочесть еще раз и запомнить! Удачи! :)