Триггеры. Базы данных

1.

1
Базы данных 2 (продвинутый)
Триггеры (Trigger)
Различные типы триггеров
Основные принципы построения триггеров
Виды DML триггеров
Типы событий триггеров
Создание триггеров

2. Цели

Триггеры
Цели
После завершения данного урока Вы узнаете
следующее:
• Описание различных типов триггеров
• Описание триггеров базы данных и их
использование
• Создание триггеров
• Описание правил исполнения триггеров
• Удаление триггеров БД
10-2

3. Типы триггеров

Триггеры
Типы триггеров
Триггер:
• Это PL/SQL блок или PL/SQL процедура
ассоциируемая с таблицей, представлением,
схемой или базой данных.
• Выполняется всякий раз, когда происходит
конкретное событие.
• Может быть следующим:
– Триггер приложения: запускается всякий раз,
при выполнении определенных событий
приложения
– Триггеры БД: запускается всякий раз при
выполнении событий с данными (DML) или
системных событий (соединение или shutdown) ,
происходящих на уровне схемы или базы.
10-3

4. Основные принципы построения триггеров

Триггеры
Основные принципы построения триггеров
Вы можете создать триггер:
– Чтобы выполнять связанные действия
– Чтобы централизовать глобальные действия
Вы не должны разрабатывать триггеры:
– где функциональное назначение уже создано в
сервер Oracle
– которые дублируют другие триггеры
10-4
Вы можете создавать хранимые процедуры и
затем вызывать их в триггере, если код PL/SQL
очень объемный.
Чрезмерное использование триггеров может
привести к сложной внутренней зависимости,
которую трудно будет поддерживать в
большом приложении.

5. Виды DML триггеров

Триггеры
Виды DML триггеров
Тип триггера определяется его выполнением для
каждой записи или только один раз.
• Триггер уровня оператора:
– Выполняется один раз для события,
вызвавшего этот триггер
– Создается по умолчанию
– Выполняется один раз, даже если нет ни одной
строки, подвергнувшейся воздействию
оператора
Триггер уровня строки:
10-5
– Выполняется один раз для каждой строки ,
подвергнувшейся воздействию оператора
– Не будет выполняться, если нет ни одной
строки, подвергнувшейся воздействию
оператора
– Используется специальное предложение
FOR EACH ROW

6. Типы событий триггеров и их тела

Триггеры
Типы событий триггеров и их тела
События триггеров:
• Определяются видом DML оператора,
исполняющего триггер
• Типы:
– INSERT
– UPDATE [OF column]
– DELETE
Тело триггера:
• Определяет действие, которое надо выполнить
• Является PL/SQL блоком или вызовом (CALL)
процедуры
10-6

7. Trigger Timing

Триггеры
Trigger Timing
BEFORE: активизируется перед выполнением
команды, вызвавшей активизацию триггера.
AFTER: активизируется после выполнения
команды, вызвавшей активизацию триггера.
INSTEAD OF: выполняется вместо команды,
вызвавшей активизацию триггера.
10-7

8. Триггеры

Триггеры
CREATE [OR REPLACE] TRIGGER trigger_name
timing
event1 [OR event2 OR event3]
ON object_name
[[REFERENCING OLD AS old | NEW AS new]
[FOR EACH ROW]
[WHEN (condition)]]
trigger_body
ON object_name
- таблица
или представление, к
к-й. привязан триггер
FOR EACH ROW – триггер строк, к-й
активизируется
для каждой строки таблицы.
trigger_body
- блок PL/SQL, к-й выполняется
при10-8
активации триггера.

9. DML Триггеры

Триггеры
CREATE [OR REPLACE] TRIGGER trigger_name
timing
event1 [OR event2 OR event3]
ON object_name
[[REFERENCING OLD AS old | NEW AS new]
[FOR EACH ROW]
[WHEN (condition)]]
trigger_body
В теле триггера м. использовать предикаты
сравнения:
Inserting – возвращает ИСТИНА (TRUE) в случае, if
командой, вызвавшей активацию триггера явл-ся
Insert.
Deleting
Updating
10-9

10. DML Триггеры

Триггеры
DML Триггеры
CREATE [OR REPLACE] TRIGGER trigger_name
timing
event1 [OR event2 OR event3]
ON object_name
[[REFERENCING OLD AS old | NEW AS new]
FOR EACH ROW
[WHEN (condition)]]
trigger_body
Триггер уровня оператора выполняется один раз для DML
оператора.
• Триггер уровня строки выполняется столько раз, сколько
записей подвергается действию оператора DML.
Note: Имя триггера должно быть уникально на уровне схемы.
10-10

11. Очередность выполнения триггеров

Триггеры
Очередность выполнения триггеров
Рассмотрим очередность выполнения триггеров на
таблице при добавлении одной записи:
DML оператор
INSERT INTO departments
(department_id,department_name,
location_id)
VALUES (400, 'CONSULTING', 2400);
Triggering action
…
10-11
BEFORE statement
trigger
BEFORE row trigger
AFTER row trigger
AFTER statement trigger

12. Очередность выполнения триггеров

Триггеры
Очередность выполнения триггеров
Рассмотрим очередность выполнения триггеров на
таблице при изменении нескольких записей:
UPDATE employees
SET salary = salary * 1.1
WHERE department_id = 30;
BEFORE statement trigger
BEFORE row trigger
AFTER row trigger
...
BEFORE row trigger
AFTER
row trigger
...
AFTER statement trigger
10-12

13. Создание DML триггера уровня оператора

Триггеры
Создание DML триггера уровня оператора
Application
INSERT INTO EMPLOYEES...;
EMPLOYEES table
SECURE_EMP trigger
CREATE OR REPLACE TRIGGER secure_emp
BEFORE INSERT ON employees BEGIN
IF (TO_CHAR(SYSDATE,'DY') IN ('SAT','SUN')) OR
(TO_CHAR(SYSDATE,'HH24:MI')
NOT BETWEEN '08:00' AND '18:00') THEN
RAISE_APPLICATION_ERROR(-20500, 'You may insert'
||' into EMPLOYEES table only during '
||' business hours.');
END IF;
END;
10-13

14. Создание DML триггера уровня оператора

Триггеры
Создание DML триггера уровня оператора
CREATE OR REPLACE TRIGGER secure_emp
BEFORE INSERT
ON employees
BEGIN
IF (TO_CHAR(SYSDATE,'DY') IN
('SAT','SUN')) OR
(TO_CHAR(SYSDATE,'HH24:MI')
NOT BETWEEN '08:00' AND
'18:00') THEN
RAISE_APPLICATION_ERROR(-20500, 'You
may insert'
||' into EMPLOYEES table only during
'
||' business hours.');
END IF;
END;
10-14

15. Тестирование SECURE_EMP

Триггеры
Тестирование SECURE_EMP
INSERT INTO employees (employee_id,
last_name,
first_name, email,
hire_date,
job_id, salary,
department_id)
VALUES (300, 'Smith', 'Rob', 'RSMITH',
SYSDATE,
'IT_PROG', 4500, 60);
10-15

16. Использование условных предикатов

Триггеры
Использование условных предикатов
CREATE OR REPLACE TRIGGER secure_emp BEFORE
INSERT OR UPDATE OR DELETE ON employees BEGIN
IF (TO_CHAR(SYSDATE,'DY') IN ('SAT','SUN')) OR
(TO_CHAR(SYSDATE,'HH24')
NOT BETWEEN '08' AND '18') THEN
IF DELETING THEN RAISE_APPLICATION_ERROR(
-20502,'You may delete from EMPLOYEES table'||
'only during business hours.');
ELSIF INSERTING THEN RAISE_APPLICATION_ERROR(
-20500,'You may insert into EMPLOYEES table'||
'only during business hours.');
ELSIF UPDATING('SALARY') THEN
RAISE_APPLICATION_ERROR(-20503, 'You may '||
'update SALARY only during business hours.');
ELSE RAISE_APPLICATION_ERROR(-20504,'You may'||
' update EMPLOYEES table only during'||
' normal hours.');
END IF;
END IF;
END;
10-16

17. Переменные (квалификаторы) NEW и OLD

Триггеры
В теле триггера строк (FOR EACH ROW) можно
использовать переменные типа запись NEW и OLD.
• NEW - переменная содержит новую строку при
вставке или новую версию строки при обновлении.
• OLD- переменная содержит старую версию строку
при обновлении или удаляемую строку при удалении.
Обращение к переменным NEW и OLD
осуществляется так же, как и при обращении к
переменным привязки (указанием перед именем
переменной двоеточия).
10-17

18. Создание DML триггеров уровня записи

Триггеры
Создание DML триггеров уровня записи
CREATE OR REPLACE TRIGGER restrict_salary
BEFORE INSERT OR UPDATE OF salary
ON employees
FOR EACH ROW
BEGIN
IF NOT (:NEW.job_id IN ('AD_PRES',
'AD_VP'))
AND :NEW.salary > 15000 THEN
RAISE_APPLICATION_ERROR (-20202,
'Employee cannot earn more than
$15,000.');
END IF;
END;
/
10-18

19. Использование квалификаторов OLD и NEW

Триггеры
Использование квалификаторов OLD и NEW
CREATE OR REPLACE TRIGGER audit_emp_values
AFTER DELETE OR INSERT OR UPDATE ON employees
FOR EACH ROW
BEGIN
INSERT INTO audit_emp(user_name, time_stamp, id,
old_last_name, new_last_name, old_title,
new_title, old_salary, new_salary)
VALUES (USER, SYSDATE, :OLD.employee_id,
:OLD.last_name, :NEW.last_name, :OLD.job_id,
:NEW.job_id, :OLD.salary, :NEW.salary);
END;
/
10-19

20. Использование квалификаторов OLD и NEW: Пример использования audit_emp

Триггеры
Использование квалификаторов OLD и NEW:
Пример использования audit_emp
INSERT INTO employees
(employee_id, last_name, job_id, salary, ...)
VALUES (999, 'Temp emp', 'SA_REP', 6000,...);
UPDATE employees
SET salary = 7000, last_name = 'Smith'
WHERE employee_id = 999;
SELECT user_name, timestamp, ...
FROM audit_emp;
10-20

21. Ограничения триггеров уровня записи: пример

Триггеры
Ограничения триггеров уровня записи: пример
CREATE OR REPLACE TRIGGER derive_commission_pct
BEFORE INSERT OR UPDATE OF salary ON employees
FOR EACH ROW
WHEN (NEW.job_id = 'SA_REP')
BEGIN
IF INSERTING THEN
:NEW.commission_pct := 0;
ELSIF :OLD.commission_pct IS NULL THEN
:NEW.commission_pct := 0;
ELSE
:NEW.commission_pct := :OLD.commission_pct+0.05;
END IF;
END;
/
10-21

22. Итоги по модели исполнения триггеров

Триггеры
Итоги по модели исполнения триггеров
1. Выполняются все BEFORE STATEMENT триггера.
2. Цикл для каждой подвергающейся записи:
a. Выполняются все BEFORE ROW триггера.
b. Выполняется DML оператор и проверяются
ограничения (constraint checking).
c. Выполняются все AFTER ROW триггеры.
3. Выполняются все AFTER STATEMENT триггера.
Note: проверка целостности может быть задержана
пока не будет выполняться команда commit.
10-22

23. Выполнение ограничения целостности через триггер

Триггеры
Выполнение ограничения целостности
через триггер
UPDATE employees SET department_id = 999
WHERE employee_id = 170;
-- Integrity constraint violation error
CREATE OR REPLACE TRIGGER employee_dept_fk_trg
AFTER UPDATE OF department_id
ON employees FOR EACH ROW
BEGIN
INSERT INTO departments VALUES(:new.department_id,
'Dept '||:new.department_id, NULL, NULL);
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
NULL; -- mask exception if department exists
END;
/
UPDATE employees SET department_id = 999
WHERE employee_id = 170;
-- Successful after trigger is fired
10-23

24. INSTEAD OF триггеры – триггеры замещения

Триггеры
INSTEAD OF триггеры – триггеры замещения
Application
INSERT INTO my_view
. . .;
INSTEAD OF trigger
MY_VIEW
10-24
INSERT
TABLE1
UPDATE
TABLE2

25. Создание INSTEAD OF триггеров

Триггеры
Создание INSTEAD OF триггеров
Выполнение INSERT в представление EMP_DETAILS,
которое построено на основе таблиц EMPLOYEES и
DEPARTMENTS:
INSERT INTO emp_details
VALUES (9001,'ABBOTT',3000, 10, 'Administration');
INSTEAD OF INSERT
into EMP_DETAILS
1
2 INSERT into NEW_EMPS
…
10-25
3 UPDATE NEW_DEPTS
…

26. Создание INSTEAD OF триггеров

Триггеры
Создание INSTEAD OF триггеров
Используйте INSTEAD OF при определении
триггеров на представление:
CREATE TABLE new_emps AS
SELECT employee_id,last_name,salary,department_id
FROM employees;
CREATE TABLE new_depts AS
SELECT d.department_id,d.department_name,
sum(e.salary) dept_sal
FROM employees e, departments d
WHERE e.department_id = d.department_id;
CREATE VIEW emp_details AS
SELECT e.employee_id, e.last_name, e.salary,
e.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id
GROUP BY d.department_id,d.department_name;
10-26

27. Сравнение триггеров БД и хранимых процедур

Триггеры
Сравнение триггеров БД и хранимых
процедур
Triggers
Procedures
Опред-ся CREATE TRIGGER
Опред-ся CREATE PROCEDURE
Словарь данных
USER_TRIGGERS.
Словарь данных USER_SOURCE.
Вызывается через DML
Вызывается явно
COMMIT, SAVEPOINT и ROLLBACK
COMMIT, SAVEPOINT и ROLLBACK поддерживаются.
не поддерживаются.
10-27

28. Сравнение триггеров и Oracle Forms триггеров

Триггеры
Сравнение триггеров
и Oracle Forms триггеров
INSERT INTO EMPLOYEES
. . .;
EMPLOYEES table
…
10-28
CHECK_SAL trigger
BEFORE
INSERT
row

29. Управление триггерами

Триггеры
Управление триггерами
Disable или reenable триггера:
ALTER TRIGGER trigger_name DISABLE | ENABLE
Disable или reenable всех триггеров таблицы:
ALTER TABLE table_name DISABLE | ENABLE
ALL TRIGGERS
Рекомпиляция триггеров таблицы:
ALTER TRIGGER trigger_name COMPILE
10-29

30. Удаление триггеров

Триггеры
Удаление триггеров
Для удаление триггеров из БД, используйте DROP
TRIGGER:
DROP TRIGGER trigger_name;
Пример:
DROP TRIGGER secure_emp;
Note: Все триггера таблицы удаляются при
удалении самой таблицы.
10-30

31. Тестирование триггеров

Триггеры
Тестирование триггеров
10-31
Проверяйте каждую триггерную операцию, как
если бы она была не триггерной.
Тестируйте каждый случай предложения WHEN.
Заставьте выполниться триггер напрямую
через операции с данными, а также через
вызов процедур.
Проверьте влияние триггера на другие
триггеры.
Проверьте влияние других триггеров на
триггер.

32. Итоги

Триггеры
Итоги
Данный урок охватил следующие темы:
• Создание триггеров БД, которые вызываются
посредством DML операторов
• Создание триггеров уровня оператора и уровня
записи
• Использование правил выполнения триггеров
• Enable, disable и управление триггерами
• Разработка стратегии проверки триггеров
• Удаление триггеров из БД
10-32

33. Практика:

Триггеры
Практика:
Эта практика охватывает следующие темы:
• Создание триггеров уровня записи
• Создание триггеров уровня оператора
• Вызов процедур из триггеров
10-33