Лабораторная работа №5. Триггеры базы данных

1. Цель работы:

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

  2. Методические указания

Лабораторная работа направлена на получение навыков создания и работы с триггерами базы данных, активизацию триггеров не только операциями DML, выполняемыми над таблицами и представлениями, но и различными событиями.

Требования к результатам выполнения лабораторного практикума:

·       при выполнении задания необходимо сопровождать все реализованные процедуры и функции набором тестовых входных и выходных данных  и описаниями к ним;

·       при внесении изменений в базу данных с помощью языка DML использовать операторы управления транзакциями, в частности оператор COMMIT или ROLLBACK, для их корректного завершения;

·       по завершении выполнения задания составить отчет о проделанной работе.

При составлении и оформлении отчета следует придерживаться рекомендаций, представленных на следующей странице: http://unesco.kemsu.ru/student/rule/rule.html.

3. Теоретический материал

Помимо процедур и функций, еще одним видом именованных блоков PL/SQL являются триггеры. Триггеры во многом схожи с подпрограммами, но некоторые их характеристики отличаются.

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

Триггеры похожи на процедуры и функции тем, что также являются именованными блоками PL/SQL и имеют раздел объявлений, выполняемый раздел и раздел обработки исключительных ситуаций. Подобно модулям, триггеры хранятся как автономные объекты в базе данных и не могут храниться локально в блоке или модуле. Процедура вызывается явным образом из другого блока, при вызове ей могут передаваться различные аргументы. Триггер же выполняется неявно всякий раз, когда происходит запускающее его событие, и триггер не имеет аргументов. Акт выполнения триггера называется его активизацией (firing). Событием, запускающим триггер, является операция DML (INSERT, UPDATE или DELETE), выполняемая над таблицей или представлением базы данных. В OracleSi эти функции расширены: триггер может срабатывать на системное событие, например на запуск или останов базы данных, а также на определенные виды операций DDL.

Триггеры можно использовать:

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

-     Для контроля за информацией, хранимой в таблице, посредством регистрации вносимых изменений и пользователей, производящих эти изменения.

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

-     Для публикации информации о различных событиях в среде "публикация- подписка".

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

В Oracle8\ и выше можно создавать триггеры на PL/SQL или на других языках программирования, еели онм вызываются как внешние подпрограммыю.

Триггеры DML

Триггер DML активизируется оператором DML, и тип триггера определяется типом этого оператора. Триггеры DML задаются для операций ввода, обновления и удаления информации (INSERT, UPDATE, DELETE). Они активизируются до или после операции, на уровне строки или оператора.

Предположим, что требуется отслеживать статистические показатели, касающиеся различных профилирующих дисциплин студентов, в том числе количество зарегистрированных студентов и общее число полученных зачетов. Результаты будут храниться в таблице major_stats:

CREATE TABLE major_stats (

major VARCHAR2(30)

total_credits NUMBER,

total_students NUMBER);

Чтобы информация в таблице major_stats была самой свежей, создадим триггер для таблицы students, который будет обновлять major_stats всякий раз при изменении students. Назовем этот триггер UpdateMajorStats. Он будет срабатывать после выполнения любой операции DML над students. Тело триггера обращается к таблице students с запросом и обновляет статистические показатели таблицы major_stats свежей информацией:

CREATE OR REPLACE TRIGGER UpdateMajorStats

/* Обновляет таблицу major_stats, отслеживая все

изменения, вносимые в таблицу students. */

AFTER INSERT OR DELETE OR UPDATE ON students

DECLARE

CURSOR c_Statistics IS

SELECT major, COUNT(*) total_students,

SUM(current_credits) total_credits

FROM students

GROUP BY major;

BEGIN

/* Сначала удалим информацию из major_stats, очистив статистические данные. Это необходимо для учета удаления всех студентов данного профиля. */

DELETE FROM major_stats;

/* Теперь в цикле просмотрим информацию по каждой дисциплине и

введем соответствующую строку в major_stats. */

FOR v_StatsRecord in c_Statistics LOOP

INSERT INTO major_stats (major, total_credits, total_students)

VALUES (v_StatsRecord.major, v_StatsRecord.total_credits,

v_StatsRecord.total_students);

END LOOP;

END UpdateMajorStats;

Операторный триггер может активизироваться операторами нескольких видов. Например, UpdateMajorStats срабатывает на операторы INSERT, UPDATE и DELETE. Активизирующее событие указывает одну или несколько операций DML, вызывающих выполнение триггера.

Триггеры замещения

В OracleS предлагается еще один вид триггеров. Триггеры замещения (instead of) можно создавать только для представлений (либо объектных, либо реляционных). В отличие от триггеров DML, которые выполняются в дополнение к операторам DML, триггеры замещения выполняются вместо операторов DML, вызывающих их срабатывание. Триггеры замещения должны быть строковыми триггерами. Для примера рассмотрим представление classes_rooms:

CREATE OR REPLACE VIEW classes_rooms AS

SELECT department, course, building, room_number

FROM rooms, classes

WHERE rooms.room_id = classes.room_id;

Ввести информацию непосредственно в это представление нельзя, так как это соединение двух таблиц, и при вводе необходимо модифицировать обе таблицы, как показывает следующий сеанс SQL*Plus:

INSERT INTO classes_rooms (department, course, building, room_number)

VALUES ('MUS', 100, 'Music Building', 200);

INSERT INTO classes_rooms (department, course, building, room_number)

***

ERROR at line 1:

ORA-01776: cannot modify more than one base table through a join view

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

CREATE TRIGGER ClassesRoomsInsert

INSTEAD OF INSERT ON classes_rooms

DECLARE

v_roomID rooms.room_id%TYPE;

BEGIN

-- Сначала определим идентификатор аудитории.

SELECT room_id

INTO v_roomID

FROM rooms

WHERE building = :new.building

AND room_number = :new.room_number;

-- А теперь обновим группу.

UPDATE CLASSES

SET room id = v roomlD

WHERE department = :new.department

AND course = :new.course;

END ClassesRoomsInsert;

С помощью триггера ClassesRoomsInsert оператор INSERT выполняется успешно и делает именно то, что нужно. В данном случае триггер ClassesRoomsInsert не содержит проверки на ошибки. Ниже, в разделе "Создание триггеров замещения", такая проверка будет добавлена.

Системные триггеры

В Огас1е8ги выше существует третий тип триггеров. Системный триггер активизируется не на операцию DML, выполняемую над таблицей, а на системное событие, например, на запуск или останов базы данных. Системные триггеры срабатывают и на операции DDL, такие как создание таблицы. Предположим, что необходимо регистрировать моменты создания объектов словаря данных. Это можно сделать, создав следующую таблицу:

CREATE TABLE ddl_creations (

user_id VARCHAR2(30),

object_type VARCHAR2(20),

object_name VARCHAR2(30),

object_owner VARCHAR2(30),

creation date DATE);

После этого можно создать системный триггер для регистрации нужных сведений. Триггер LogCreations регистрирует в таблице ddl_creations сведения о только что созданных объектах после каждой операции CREATE в текущей схеме.

CREATE OR REPLACE TRIGGER LogCreations

AFTER CREATE ON SCHEMA

BEGIN

INSERT INTO ddl_creations (user_id, object_type, object_name,

object_owner, creation_date)

VALUES (USER, SYS.DICTIONARY_OBJ_TYPE,

                 SYS.DICTIONARY_OBJ_NAME,

                SYS.DICTIONARY_OBJ_OWNER, SYSDATE);

END LogCreations;

Создание триггеров

Вне зависимости от типа все триггеры создаются одинаково. Общий синтаксис создания триггера таков:

CREATE [OR REPLACE] TRIGGER имя_триггера

{BEFORE | AFTER | INSTEAD OF] активизирующее_событие

ссылочное_предложение

[WHEN условие_срабатывания]

[FOR EACH ROW]

тело_триггера;

где имя_триггера — это имя триггера, активизирующее_событие указывает событие, которое запускает триггер (может содержать конкретную таблицу или представление), а тело_триггера — основной программный текст триггера. Ссылочное_предложение используется для ссылки на данные в модифицируемой в конкретный момент строке с помощью другого имени. Если присутствует условие_срабатывания. в конструкции WHEN (когда), то оно оценивается первым. Тело триггера выполняется только в том случае, если это условие истинно.

Тело триггера не может превышать 32 Кбайт. Если триггер больше, то его следует уменьшить, перенеся часть программного текста в отдельно компилируемые модули или хранимые процедуры и вызывая их в теле триггера. Ограничение размера тела триггеров обусловлено частотой их выполнения.

Создание триггеров DML

Триггер DML активизируется операцией INSERT (ввод), UPDATE (обновление) или DELETE (удаление), выполняемой над таблицей базы данных. Триггеры могут активизироваться до (BEFORE) или после (AFTER) операции и действовать на уровне строки или оператора. Тип триггера определяется комбинацией этих факторов. Существует 12 возможных видов: 3 оператора х 2 момента времени х 2 уровня. Ниже приведены примеры правильных триггеров DML:

-       До выполнения операции обновления на операторном уровне.

-       После выполнения операции ввода на уровне строк.

-       До выполнения операции удаления на уровне строк.

Возможные варианты триггеров представлены в таблице 11.1. Кроме того, триггер может активизироваться несколькими типами операторов DML, выполняемых над конкретной таблицей: например, INSERT и UPDATE. Код триггера выполняется вместе с активизирующим оператором как часть одной транзакции.

Для таблицы можно создать любое число триггеров каждого вида, в том числе несколько триггеров определенного DML-типа. Например, можно описать два операторных триггера AFTER DELETE. Триггеры одного и того же типа будут срабатывать по очереди. (Порядок активизации триггеров см. ниже).

Таблица 1. Виды триггеров DML

До PL/SQL 2.1 для таблицы можно было создать максимум 12 триггеров — по одному каждого вида. Поэтому инициализационный параметр COMPATIBLE должен быть установлен в значение 7.1 или выше, чтобы можно было использовать для таблицы повторяющиеся триггеры одного типа.

В активизирующем событии триггера DML указывается имя таблицы (и столбца), для которой должен срабатывать триггер. В OracleSz и выше триггер может активизироваться еще и для столбца вложенной таблицы.

Порядок активизации триггеров DML

Триггеры активизируются при выполнении оператора DML. Алгоритм выполнения оператора DML таков:

1.     Выполняются операторные триггеры BEFORE (при их наличии).

2.     Для каждой строки, на которую воздействует оператор:

a.      Выполняются строковые триггеры BEFORE (при их наличии).

b.     Выполняется собственно оператор.

c.     Выполняются строковые триггеры AFTER (при их наличии).

3.     Выполняются операторные триггеры AFTER (при их наличии).

В качестве примера создадим различные триггеры UPDATE для таблицы classes. Мы создадим три строковых триггера BEFORE и два операторных триггера AFTER:

CREATE SEQUENCE trig_seq

START WITH 1

INCREMENT BY 1;

CREATE OR REPLACE PACKAGE TrigPackage AS

-- Глобальный счетчик для использования в триггерах

v_Counter NUMBER;

END TrigPackage;

CREATE OR REPLACE TRIGGER classesBStatement

BEFORE UPDATE ON classes

BEGIN

-- Сначала сбросим счетчик.

TrigPackage.v_Counter := 0;

INSERT INTO temp_table (num_col, char_col)

VALUES (trig_seq.NEXTVAL,

'Before Statement: counter = ' | I TrigPackage.v_Counter);

-- А теперь увеличим его значение для следующего триггера.

TrigPackage.v_Counter := TrigPackage.v_Counter+ 1;

END ClassesBStatement;

CREATE OR REPLACE TRIGGER ClassesAStatementl

AFTER UPDATE ON classes

BEGIN

INSERT INTO temp_table (num_col. char_col)

VALUES (trig'_seq.NEXTVAL,

'After Statement 1: counter = ' | TrigPackage.v_Counter);

-- Увеличим для следующего триггера.

TrigPackage.v_Counter := TrigPackage.v_Counter+ 1;

END ClassesAStatementl;

CREATE OR REPLACE TRIGGER ClassesAStatement2

AFTER UPDATE ON classes

BEGIN

INSERT INTO temp_table (num_col, char_col)

VALUES (trig_seq.NEXTVAL,

'After Statement 2: counter = ' || TrigPackage.v_Counter);

-- Увеличим для следующего триггера.

TrigPackage.v_Counter := TrigPackage.v_Counter+ 1;

END ClassesAStatement2;

CREATE OR REPLACE TRIGGER ClassesBRowl

BEFORE UPDATE ON classes

FOR EACH ROW

BEGIN

INSERT INTO temp_table (num_col, char_col)

VALUES (trig_seq.NEXTVAL,

'Before Row 1: counter = ' || TrigPackage.v_Counter);

-- Увеличим для следующего триггера.

TrigPackage.vJDounter := TrigPackage.v_Counter+ 1;

END ClassesBRowl;

CREATE OR REPLACE TRIGGER ClassesBRow2

BEFORE UPDATE ON classes

FOR EACH ROW

BEGIN

INSERT INTO temp_table (num_col, char__col)

VALUES (trig_seq.NEXTVAL,

'Before Row 2: counter = ' | TrigPackage.v_Counter);

-- Увеличим для следующего триггера.

TrigPackage.v_Counter := TrigPackage.v_Counter+ 1;

END ClassesBRow2;

CREATE OR REPLACE TRIGGER ClassesBRowS

BEFORE UPDATE ON classes

FOR EACH ROW

BEGIN

INSERT INTO temp_table (num_col, char_col)

VALUES (trig_seq.NEXTVAL,

'Before Row 3: counter = ' || TrigPackage.v_Counter);

-- Увеличим для следующего триггера.

TrigPackage.v_Counter := TrigPackage,v_Counter+ 1;

END ClassesBRowS;

CREATE OR REPLACE TRIGGER ClassesARow

AFTER UPDATE ON classes

FOR EACH ROW

BEGIN

INSERT INTO temp_table (num_col, char_col)

VALUES (trig_seq.NEXTVAL,

'After Row: counter = ' || TrigPackage.v_Counter);

-- Увеличим для следующего триггера.

TrigPackage.v_Counter := TrigPackage.v_Counter+ 1;

END ClassesARow;

Выполним следующий оператор UPDATE:

UPDATE classes

SET num_credits = 4

WHERE department IN ('HIS', ' C S ' ) ;

Этот оператор воздействует на четыре строки. Операторные триггеры BEFORE и AFTER выполняются по разу, а строковые триггеры BEFORE и AFTER — по четыре раза. Если после этого считать информацию в temp_table, то будет получено следующее:

SELECT * FROM temp_table

ORDER BY num_col;

NUM_COL      CHAR_COL

--------------       -------------------------

1 Before Statement: counter = 0

2 Before Row 3: counter = 1

3 Before Row 2: counter = 2

4 Before Row 1: counter = 3

5 After Row : counter = A

6 Before Row 3: counter = 5

7 Before Row 2: counter = 6

8 Before Row 1: counter = 7

9 After Row : counter = 8

10 Before Row 3: counter = 9

11 Before Row 2: counter = 10

12 Before Row 1: counter = 11

13 After Row : counter = 12

14 Before Row 3: counter = 13

15 Before Row 2: counter = 14

16 Before Row 1: counter = 15

17 After Row : counter = 16

18 After Statement 2: counter = 17

19 After Statement 1: counter = 18

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

Порядок, в котором активизируются триггеры одного вида, не определен. Из приведенного примера следует, что каждый триггер видит изменения, вносимые более ранними триггерами. Если порядок важен, следует объединить все операции в один триггер.

При создании журнала моментальных снимков таблицы Oracle w автоматически создает для нее строковый триггер AFTER ROW, обновляющий этот журнал после каждого оператора DML Следует учитывать этот факт при создании дополнительного строкового триггера AFTER для данной таблицы. Существуют и другие ограничения на использование триггеров и моментальных снимков (известных как материализованные представления в Oracle®).

Идентификаторы корреляции в строковых триггерах

Строковый триггер запускается один раз для каждой строки, обрабатываемой активизирующим оператором. Внутри триггера можно обращаться к данным строки, обрабатываемой в данный момент. Для этого служат два идентификатора корреляции — : old и : new. Идентификатор корреляции (correlation identifier) — это переменная привязки PL/SQL особого рода. Двоеточие перед идентификатором указывает на то, что это переменные привязки (подобны базовым переменным, используемым во встроенном PL/SQL), а не обычные переменные PL/ SQL. Компилятор PL/ SQL рассматривает их как записи типа

активизирующая_таблица%ROWTYPE

где активизирующая_таблица — это таблица, для которой создан триггер. Следовательно, ссылка типа

:nev.noлe

будет достоверна, если только поле является полем активизирующей таблицы. Назначение : old и : new описано в таблице 11.2. Хотя синтаксически они рассматриваются в качестве записей, фактически эти идентификаторы записями не являются. Именно поэтому их называют псевдозаписями.

Псевдозапись : old не определена для операторов INSERT, а для операторов DELETE не определена псевдозапись : new. В случае использования . 'old в операторе INSERT или : new в операторе DELETE компилятор PL/SQL не будет генерировать ошибку, но значения полей обеих записей будут NULL.

В OracleSz определен еще один идентификатор корреляции — : parent. Если триггер создается для вложенной таблицы, : old и : new ссылаются на ее строки, а : parent — на текущую строку родительской таблицы.

Таблица 2. Идентификаторы корреляции :old и :new

4. Порядок выполнения работы

Изучить предлагаемый теоретический материал.

Выполнить следующие задания:

1. Для отслеживания изменений информации о сотрудниках, создайте таблицу s_emp_log и напишите триггер, заносящий в s_emp_log информацию о времени изменения и информации о сотруднике до изменения.

2. Для отслеживания удалений информации о сотрудниках, напишите триггер, заносящий в s_emp_log удалённую информацию, а также время удаления.

3. Напишите триггер для таблице s_item, позволяющий контролировать изменение цены товара.  Если новая цена отличается от старой более чем на 30%, выдаётся соответствующее сообщение и запрещается изменения данных. Реализовать используя исключения.

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

5. Создайте таблицу, в которую будут заноситься данные регистрации моментов создания объектов словаря данных:

  CREATE TABLE  ddl_creations (

  user_id                 VARCHAR2(30),

  object_type VARCHAR2(30),

  object_name         VARCHAR2(30),

  object_owner   VARCHAR2(30),

  creation date          DATE);

Создайте системный триггер для регистрации нужных сведений.

6. Создайте таблицу, в которую будут заноситься данные о регистрации ошибок компиляции и ошибок этапа выполнения:

  CREATE TABLE error_log(

  timestamp    DATE,

  username     VARCHAR2(30),

  instanse               NUMBER,

  database_name    VARCHAR2(50),

  error_stack VARCHAR2(2000));

Создайте системный триггер для регистрации нужных сведений.

Сгенерируйте несколько ошибок и посмотрите, правильно триггер регистрирует их.

7. Создайте представление, содержащее имя отдела, номер региона, Фамилию, имя, должность и номер сотрудника.

  Операции DML для данного представления определим по следующим правилам:

INSERT – Назначить отдел, введенному сотруднику. В       результате s_dept (при необходимости s_region)              обновляются.

UPDATE – Изменить отдел, назначенный сотруднику. Это может привести к обновлению или s_emp или s_dept, в зависимости от того, какой столбец представления обновляется.

DELETE – Очистить идентификатор отдела для сотрудника (сотрудник не зачислен ни в один отдел). В результате s_emp обновляется: ID устанавливается в значении NULL.

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

  Входные данные процедуры – ФИО, должность сотрудника, название отдела. Заработную плату сотруднику определить как среднюю з/п всех сотрудников фирмы.

5. Содержание отчета

В отчете следует указать:

1. Цель работы

2. Введение

3. Программно-аппаратные средства, используемые при выполнении работы.

4. Основную часть (описание самой работы), выполненную согласно требованиям к результатам выполнения лабораторного практикума.

5. Заключение (выводы)

6. Список используемой литературы

Литература:

1. Баженова И.Ю. Oracle 8/8i. Уроки программирования. - М.: "ДИАЛОГ-МИФИ",2000.

2. Дейт К. Дж. Введение в системы баз данных. 7-е изд. - М.-СПб-К:"Диалектика", 2001.

3. Скотт Урман. "ORACLE 8. Программирование на языке PL/SQL".
   (М.: "Лори", 1999, 608 стр.); [оригинал: Scott Urman "Oracle8 PL/SQL Programming" Osborne/McGraw-Hill, 1997]

4. В.А.Кравчук, Ю.П.Бегус, В.А.Габзовский. "Введение в Oracle PL/SQL". ‑ Киев, Издательство "ДиаСофт", 1998, - 400 с.