Продолжаем разбирать триггеры. Каждый из них, как и любой другой объект БД, после создания хранится в словаре данных в виде P-кода. Ранее до версии Oracle 7.3 триггеры хранились в словаре данных, в виде исходного кода. И каждые раз при вызове компилировались, а затем исполнялись. В более старших версиях Oracle, триггеры хранятся уже в скомпилированном виде. В результате, так же как и модули и подпрограммы, могут автоматически становиться недостоверными. Но, становясь недостоверным, триггер компилируется при следующей его активации. Активация триггеров, как вы уже знаете происходит при выполнении операторов DML. Порядок активации триггеров в большинстве случаев таков:
Давайте разберемся со всем этим более детально, чтобы четко представлять, всю картину работы триггеров таблиц БД. Но для начала, создадим пару табличек и наполним их данными. В дальнейшем будем работать с ними как учебными в этом разделе. В следствии того, что я не хочу пока менять что-либо в наших основных пяти учебных табличках. Итак, создаем первую таблицу:
CREATE TABLE MILLER.TSTTRIG ( ID NUMBER PRIMARY KEY, NM VARCHAR2(50), ROD VARCHAR2(50), INRW DATE ) /
Получаем:
SQL> CREATE TABLE MILLER.TSTTRIG 2 ( 3 ID NUMBER PRIMARY KEY, 4 NM VARCHAR2(50), 5 ROD VARCHAR2(50), 6 INRW DATE 7 ) 8 / Таблица создана.
Заполняем ее поля такими данными:
INSERT INTO TSTTRIG (ID, NM, ROD, INRW)
VALUES (7369, 'SMITH', 'CLERK', TO_DATE('17-2-2000', 'DD-MM-YYYY'))
/
INSERT INTO TSTTRIG (ID, NM, ROD, INRW)
VALUES (7370, 'JONES', 'MANAGER', TO_DATE('2-4-2001', 'DD-MM-YYYY'))
/
INSERT INTO TSTTRIG (ID, NM, ROD, INRW)
VALUES (7371, 'MILLER', 'SALESMAN', TO_DATE('20-3-2003', 'DD-MM-YYYY'))
/
INSERT INTO TSTTRIG (ID, NM, ROD, INRW)
VALUES (7372, 'SCOTT', 'ANALYST', TO_DATE('09-12-2001', 'DD-MM-YYYY'))
/
COMMIT
/
Получаем:
SQL> INSERT INTO TSTTRIG (ID, NM, ROD, INRW)
2 VALUES (7369, 'SMITH', 'CLERK', TO_DATE('17-2-2000', 'DD-MM-YYYY'))
3 /
1 строка создана.
SQL> INSERT INTO TSTTRIG (ID, NM, ROD, INRW)
2 VALUES (7370, 'JONES', 'MANAGER', TO_DATE('2-4-2001', 'DD-MM-YYYY'))
3 /
1 строка создана.
SQL> INSERT INTO TSTTRIG (ID, NM, ROD, INRW)
2 VALUES (7371, 'MILLER', 'SALESMAN', TO_DATE('20-3-2003', 'DD-MM-YYYY'))
3 /
1 строка создана.
SQL> INSERT INTO TSTTRIG (ID, NM, ROD, INRW)
2 VALUES (7372, 'SCOTT', 'ANALYST', TO_DATE('09-12-2001', 'DD-MM-YYYY'))
3 /
1 строка создана.
SQL> COMMIT
2 /
Фиксация обновлений завершена.
Итак, одна табличка готова, создаем еще одну:
CREATE TABLE MILLER.TSTSV ( ID NUMBER PRIMARY KEY, IDD VARCHAR2(50), ROD VARCHAR2(50), CONS NUMBER ) /
Получаем:
SQL> CREATE TABLE MILLER.TSTSV 2 ( 3 ID NUMBER PRIMARY KEY, 4 IDD VARCHAR2(50), 5 ROD VARCHAR2(50), 6 CONS NUMBER 7 ) 8 / Таблица создана.
Заполняем ее поля такими данными:
INSERT INTO TSTSV (ID, IDD, ROD, CONS) VALUES (1, 'SMITH', 'CLERK', 7369) / INSERT INTO TSTSV (ID, IDD, ROD, CONS) VALUES (2, 'JONES', 'MANAGER', 7370) / INSERT INTO TSTSV (ID, IDD, ROD, CONS) VALUES (3, 'MILLER', 'SALESMAN', 7371) / INSERT INTO TSTSV (ID, IDD, ROD, CONS) VALUES (4, 'SCOTT', 'ANALYST', 7372) / COMMIT /
Получаем:
SQL> INSERT INTO TSTSV (ID, IDD, ROD, CONS) 2 VALUES (1, 'SMITH', 'CLERK', 7369) 3 / 1 строка создана. SQL> INSERT INTO TSTSV (ID, IDD, ROD, CONS) 2 VALUES (2, 'JONES', 'MANAGER', 7370) 3 / 1 строка создана. SQL> INSERT INTO TSTSV (ID, IDD, ROD, CONS) 2 VALUES (3, 'MILLER', 'SALESMAN', 7371) 3 / 1 строка создана. SQL> INSERT INTO TSTSV (ID, IDD, ROD, CONS) 2 VALUES (4, 'SCOTT', 'ANALYST', 7372) 3 / 1 строка создана. SQL> COMMIT 2 / Фиксация обновлений завершена.
Строим зависимость таблицы MILLER.TSTSV от таблицы MILLER.TSTTRIG по полям CONS и ID соответственно:
ALTER TABLE MILLER.TSTSV ADD FOREIGN KEY (CONS) REFERENCES MILLER.TSTTRIG (ID) /
Получаем:
SQL> ALTER TABLE MILLER.TSTSV ADD 2 FOREIGN KEY (CONS) REFERENCES MILLER.TSTTRIG (ID) 3 / Таблица изменена.
Убедимся в том, что связь двух таблиц работает. Дадим такой оператор:
INSERT INTO TSTSV (ID, IDD, ROD, CONS) VALUES (5, NULL, NULL, 7380) /
Получаем соответственно:
SQL> INSERT INTO TSTSV (ID, IDD, ROD, CONS) 2 VALUES (5, NULL, NULL, 7380) 3 / INSERT INTO TSTSV (ID, IDD, ROD, CONS) * ошибка в строке 1: ORA-02291: нарушено ограничение целостности (MILLER.SYS_C003552) - исходный ключ не найден
Все верно! Нарушена ссылочная целостность! Так же давайте видоизменим нашу табличку из прошлых шагов MILLER.ADT, помните? Мы в нее записывали события! Проделаем следующее - добавим в нее еще два поля. Вот так:
ALTER TABLE MILLER.ADT ADD WDO VARCHAR2(50) / ALTER TABLE MILLER.ADT ADD PRIM VARCHAR2(50) /
Получаем следующее:
SQL> ALTER TABLE MILLER.ADT ADD WDO VARCHAR2(50) 2 / Таблица изменена. SQL> ALTER TABLE MILLER.ADT ADD PRIM VARCHAR2(50) 2 / Таблица изменена.
Вот теперь все готово для наших дальнейших действий. Чтобы понять в чем основное отличие операторного триггера от строкового проделаем следующие действия. Создадим для таблицы MILLER.TSTTRIG два операторных триггера по одному на каждое из двух действий BEFORE и AFTER и сработку установим на событие UPDATE. Вот так:
CREATE OR REPLACE TRIGGER BFOTST BEFORE UPDATE ON TSTTRIG DECLARE BEGIN INSERT INTO ADT(USAL, TISP, WDO, PRIM) VALUES(USER, SYSDATE, 'Update', 'Before Statement trigger'); END BFOTST; / CREATE OR REPLACE TRIGGER AFTTST AFTER UPDATE ON TSTTRIG DECLARE BEGIN INSERT INTO ADT(USAL, TISP, WDO, PRIM) VALUES(USER, SYSDATE, 'Update', 'After Statement trigger'); END AFTTST; /
Получаем:
SQL> CREATE OR REPLACE TRIGGER BFOTST 2 BEFORE UPDATE ON TSTTRIG 3 4 DECLARE 5 6 BEGIN 7 8 INSERT INTO ADT(USAL, TISP, WDO, PRIM) 9 VALUES(USER, SYSDATE, 'Update', 'Before Statement trigger'); 10 11 END BFOTST; 12 / Триггер создан. SQL> CREATE OR REPLACE TRIGGER AFTTST 2 AFTER UPDATE ON TSTTRIG 3 4 DECLARE 5 6 BEGIN 7 8 INSERT INTO ADT(USAL, TISP, WDO, PRIM) 9 VALUES(USER, SYSDATE, 'Update', 'After Statement trigger'); 10 11 END AFTTST; 12 / Триггер создан.
Операторные триггеры созданы. Теперь создадим строковые по тому же принципу, но отличаться они будут наличием конструкции FOR EACH ROW. Вот таким образом:
CREATE OR REPLACE TRIGGER BFOTSTR BEFORE UPDATE ON TSTTRIG FOR EACH ROW DECLARE BEGIN INSERT INTO ADT(USAL, TISP, WDO, PRIM) VALUES(USER, SYSDATE, 'Update', 'Before Row trigger'); END BFOTSTR; / CREATE OR REPLACE TRIGGER AFTTSTR AFTER UPDATE ON TSTTRIG FOR EACH ROW DECLARE BEGIN INSERT INTO ADT(USAL, TISP, WDO, PRIM) VALUES(USER, SYSDATE, 'Update', 'After Row trigger'); END AFTTSTR; /
Получаем:
SQL> CREATE OR REPLACE TRIGGER BFOTSTR 2 BEFORE UPDATE ON TSTTRIG 3 FOR EACH ROW 4 5 DECLARE 6 7 BEGIN 8 9 INSERT INTO ADT(USAL, TISP, WDO, PRIM) 10 VALUES(USER, SYSDATE, 'Update', 'Before Row trigger'); 11 12 END BFOTSTR; 13 / Триггер создан. SQL> CREATE OR REPLACE TRIGGER AFTTSTR 2 AFTER UPDATE ON TSTTRIG 3 FOR EACH ROW 4 5 DECLARE 6 7 BEGIN 8 9 INSERT INTO ADT(USAL, TISP, WDO, PRIM) 10 VALUES(USER, SYSDATE, 'Update', 'After Row trigger'); 11 12 END AFTTSTR; 13 / Триггер создан.
Строковые триггеры созданы. Перед тем как проверить отличия в их действиях, очистим табличку MILLER.ADT от прошлых данных:
DELETE FROM MILLER.ADT / COMMIT /
А вот теперь попробуем ввести такой оператор, для таблички MILLER.TSTTRIG:
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 / Фиксация обновлений завершена.
Изменено две строки! Так как условие оператора UPDATE соответствует в нашем случае двум записям. Верно? Смотрим содержимое таблички MILLER.ADT:
SQL> SELECT * FROM MILLER.ADT 2 / USAL TISP WDO PRIM -------------------- ----------- ----------------- ------------------------ MILLER 17.03.2004 Update Before Statement trigger MILLER 17.03.2004 Update Before Row trigger MILLER 17.03.2004 Update After Row trigger MILLER 17.03.2004 Update Before Row trigger MILLER 17.03.2004 Update After Row trigger MILLER 17.03.2004 Update After Statement trigger 6 rows selected
Думаю, очень хорошо видно, что операторный триггер сработал только два раза на BEFORE и AFTER, а вот строковый четыре раза! Именно потому, что изменили две строки! И каждый из них среагировал дважды - в результате имеем четыре сработки двух триггеров! Это нужно очень хорошо представлять и применять каждый из данного типа триггеров, именно тогда когда в этом есть необходимость! Для полноты картины вернем полям таблицы MILLER.TSTTRIG прежние значения и очистим таблицу MILLER.ADT:
UPDATE MILLER.TSTTRIG SET ROD = 'CLERK' WHERE ID = 7369 / UPDATE MILLER.TSTTRIG SET ROD = 'MANAGER' WHERE ID = 7370 / COMMIT / DELETE FROM MILLER.ADT / COMMIT /
Получим:
SQL> UPDATE MILLER.TSTTRIG 2 SET ROD = 'CLERK' 3 WHERE ID = 7369 4 / 1 строка обновлена. SQL> UPDATE MILLER.TSTTRIG 2 SET ROD = 'MANAGER' 3 WHERE ID = 7370 4 / 1 строка обновлена. SQL> COMMIT 2 / Фиксация обновлений завершена. SQL> DELETE FROM MILLER.ADT 2 / 18 строк удалено. SQL> COMMIT 2 / Фиксация обновлений завершена.
Вот пока все, что касается типов триггеров, с которыми в основном вам придется работать! Можете проделать все еще раз сначала, для лучшего запоминания! :)