Тема: Триггеры PL/SQL

DML
Триггеры замещения
Системные триггеры

«ЛОРИ». 2004. – 528 с.

поименованными блоками PL/SQL и имеют разделы объявлений, выполняемый и обработки

исключений.
Триггер запускается автоматически при возникновении определенных событий:
операции DML (ISERT, UPDATE или DELETE), выполняемой над таблицей или представлением;
системного события, например, запуска или останова БД;
определенных видов операций DDL.

реализация сложных ограничений целостности данных;
контроль

за информацией в таблицах путем регистрации изменений и пользователей;
оповещение других программ о необходимых действиях при изменении информации в таблице;
сложный контроль защиты данных или специальный аудит;
публикация информации о событиях.
Типы триггеров:
триггеры DML (операторные или строковые);
триггеры замещения;
системные триггеры.

(INSERT), удаления (DELETE ) или модификации (UPDATE) данных таблицы. Они

активизируются до или после операции, на уровне оператора или строки.
Событие, управляющее запуском триггера, описывается в виде логических условий. Триггер может запускаться и несколькими операторами, но хотя бы один оператор из трех должен быть указан в условии запуска триггера.

выполнения оператора DML:
Выполняются операторные триггеры BEFORE (при наличии).
Для каждой строки,

на которую действует оператор:
Выполняются строковые триггеры BEFORE (при наличии).
Выполняется собственно оператор DML.
Выполняются строковые триггеры AFTER (при наличии).
3. Выполняются операторные триггеры AFTER (при наличии).

условий срабатывания могут быть указаны конкретные изменяемые столбцы.
Для управления

триггерами предназначены операторы, которые могут переключать режим, разрешая или запрещая запуск триггера: — ALTER TRIGGER (изменяется режим указанного триггера) с опцией DISABLE или ENABLE и ALTER TABLE (переключается режим у всех триггеров, связанных с таблицей).

имя_триггера {BEFORE | AFTER}
{INSERT | DELETE | UPDATE [OF имя_столбца
[

, имя_столбца ... ]] }
[OR {INSERT | DELETE | UPDATE [OF имя_столбца
[ , имя_столбца ... ]] }...]
ON [имя_схемы.]{имя_таблицы \ имя_представления }
[FOR EACH ROW] [WHEN условие ]
программа_на_ PL/SQL

Необязательное ключевое слово FOR EACH ROW определяет триггер как строковый.

Необязательное ключевое слово WHEN задает дополнительное логическое условие, сужающее область событий, при наступлении которых триггер запускается.

CREATE TABLE MILLER.ADT
(
3 USAL VARCHAR2(50),
TISP DATE

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

С ее помощью и операторного триггера организуем аудит доступа к таблице MILLER.CUSTOMERS.

AFTER INSERT OR DELETE OR UPDATE ON customers
3
DECLARE

5
BEGIN
7
INSERT INTO MILLER.ADT(USAL, TISP)
VALUES(USER, SYSDATE);
10
END testTrg;
12 /.

INTO CUSTOMERS(cust_num, company, cust_rep, credit_limit)
VALUES (2200, 'MyCompany', 107, 555.5643)

/ 1 строка создана.
В таблице аудита MILLER.ADT
SQL> SELECT USAL, TO_CHAR(TISP,'DD/MM/YYYY, HH24:MI:SS') TISM
FROM ADT
/ 
Получим: USAL TISM
----------------------------------------------------
MILLER 17/03/2004, 15:25:12

KEY,
NM VARCHAR2(50),
ROD VARCHAR2(50),
INRW DATE )
Поля таблицы MILLER.TSTTRIG:
INSERT INTO TSTTRIG (ID,

NM, ROD, INRW)
VALUES (7369, 'SMITH', 'CLERK', TO_DATE('17-2-2000', 'DD-MM-YYYY'))
VALUES (7370, 'JONES', 'MANAGER', TO_DATE('2-4-2001', 'DD-MM-YYYY'))
VALUES (7371, 'MILLER', 'SALESMAN', TO_DATE('20-3-2003', 'DD-MM-YYYY'))
VALUES (7372, 'SCOTT', 'ANALYST', TO_DATE('09-12-2001', 'DD-MM-YYYY'))

триггера на моменты 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;

же принципу, но отличаются они наличием 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;

UPDATE ON TSTTRIG
FOR EACH ROW
DECLARE
BEGIN
INSERT INTO ADT(USAL, TISP, WDO, PRIM)
VALUES(USER,

SYSDATE, 'Update', 'After Row trigger');
END AFTTSTR;

Введем оператор, для таблицы MILLER.TSTTRIG:
UPDATE MILLER.TSTTRIG
SET ROD = 'SPOOKY'
WHERE ID IN (7369, 7370)
Изменено две строки! Так как условие оператора UPDATE соответствует в нашем случае двум записям.

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, а строковый четыре раза! Потому, что изменили две строки!

DML, выполняемых в дополнение к операторам DML, замещающие триггеры выполняются

вместо операторов DML, вызывающих их срабатывание. Замещающий триггер должен быть строковым.
Если представление является не модифицируемым, то для него можно создать замещающий триггер, выполняющий нужные действия и тем самым разрешающий его модификацию.
Замещающий триггер можно создать и для модифицируемого представления, если требуется дополнительная обработка информации.

Замещающие триггеры

rooms, classes
WHERE rooms. room_id = classes.room_id;
Создадим представление classes_rooms :
Ввести информацию

в это представление нельзя. Над ним можно выполнить операции обновления или удаления данных, но, скорее всего, эти действия будут некорректны.
Например, в результате выполнения над classes_rooms операции DELETE будут удалены соответствующие строки из classes.

Пример замещающего триггера

-- Определим идентификатор аудитории.
SELECT room_id
INTO v_roomID

FROM rooms
WHERE building = :new.building
AND room_number = :new.room_number;

Создадим триггер замещения и с его помощью выполним обновление базовых таблиц:

Пример замещающего триггера

SET room_id = v_roomlD
WHERE department

= :new.department
AND course = :new.course;

END ClassesRoomsInsert;

С помощью триггера ClassesRoomsInsert оператор INSERT выполняется успешно.

Пример замещающего триггера

БД.
К событиям DDL относятся операторы CREATE, DROP, ALTER.
К

событиям базы данных относятся: - запуск останов сервера, регистрация отключение пользователя БД, ошибка сервера.
Формат создания системного триггера:
CREATE OR REPLACE TRIGGER [схема.]имя_триггера
{BEFORE | AFTER}
{список_событий_DDL | список_событий_базы_данных}
ON {DATABASE | [схема.]SCHEMA}
конструкция_REFERENCING
[условие_WHEN]
тело триггера;

(разделяемых OR)
список_событий_базы_данных - одно или несколько событий БД (разделяемых

OR)

правами SYSTEM. Создадим таблицу для фиксации регистрации пользователей на уровне

БД:
SQL> CREATE TABLE SYSTEM.AUDTBASE
(
NZAP NUMBER,
POLZ VARCHAR2(20),
TMIN DATE,
OPER VARCHAR2(50)
)
8 / 

REPLACE TRIGGER FIXUSERIN
AFTER LOGON ON DATABASE
3
BEGIN

5
INSERT INTO SYSTEM.AUDTBASE(NZAP, POLZ, TMIN, OPER)
VALUES(1, USER, SYSDATE, 'UserIsLog(off)');
8
END FIXUSERIN;
/ Триггер создан.

первом сеансе:
SQL> SELECT NZAP, POLZ, TO_CHAR(TMIN,'DD.MM.YYYY HH24:MI:SS'), OPER FROM SYSTEM.AUDTBASE

/
Результат:
NZAP POLZ TO_CHAR(TMIN,'DD.MM.YYYYHH24:M OPER
--------------- ------------------------------ --------------------
1 SYS 22.03.2003 16:35:50 UserIsLog(off)
1 SYS 22.03.2003 16:35:50 UserIsLog(off)
1 SYS 22.03.2003 16:37:38 UserIsLog(off)
1 MILLER 22.03.2003 16:38:44 UserIsLog(off)
1 MILLER 22.03.2003 16:38:44 UserIsLog(off)
1 MILLER 22.03.2003 16:38:46 UserIsLog(off) 
6 строк выбрано.