Триггеры баз данных

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

· Теоретические сведения

Триггер базы данных ─ это хранимая процедура, которую можно связать с некоторой таблицей. Когда приложение выполняет SQL-оператор DML над таблицей, удовлетворяющей условиям, указанным в триггере, ORACLE автоматически активизирует или выполняет триггер. Таким образом, триггеры можно использовать для настройки реакции ORACLE на различные события приложения. Вне зависимости от типа триггера все триггеры создаются одинаково. Общий синтаксис создания таков:

сreate [ or replace ] trigger имя_триггера

{ before | after | instead of } активизирующее_событие

on имя_таблицы

[ for each row ]

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

Здесь активизирующее_событие указывает событие активации триггера (далее указывается конкретная таблица или представление).

Например, следующий триггер автоматически регистрирует изменения, вносимые в таблицу parts:

create or replace trigger parts_log

after insert or update or delete on parts

declare

stmt_type char (1);

begin

if inserting then

stmt_type:=’I’;

elsif updating then

stmt_type:=’U’;

else

stmt_type:=’D’;

end if;

insert into part_change_log

values (stmt_type, USER);

end parts_log;

Как видно из выше приведенного примера, описание триггера содержит однозначно определяемые части:

─ список операторов, активизирующих триггер, который включает insert, update и/или delete.

─ способ активизации триггера до (before) или после (after) выполнения оператора триггера в зависимости от логики конкретного приложения.

Помимо этого в описании триггера указывается, должен ли триггер активизироваться один раз (независимо от того, на какое количество строк оказывает воздействие оператор триггера ─ операторные триггеры), или для каждой изменяемой строки (такие триггеры называются строчными). Следует также отметить, что триггер может быть связан с одной и только с одной таблицей.

Приведенный выше триггер ─ это операторный триггер. В следующем примере триггер parts_log является строчным:

create or replace trigger parts_log

before insert or update or delete on parts

for each row

declare

stmt_type char (1);

begin

if inserting then

stmt_type:=’I’;

elsif updating then

stmt_type:=’U’;

else

stmt_type:=’D’;

end if;

insert into part_change_log

values (:new.id,:old.id,:new.unit_price,:old. unit_price,:new.description,:old. description, stmt_type, USER, TO_CHAR (SYSDATE, ’DD-MON-YYYY’));

end parts_log;

Из рассмотренных выше примеров видно, что в PL/SQL имеются следующие уникальные языковые конструкции для триггеров баз данных:

─ предикаты inserting, updating, deleting могут использоваться в операторах условного перехода в теле триггера;

─ значения корреляции позволяют с помощью строковых триггеров обращаться к новым и старым значениям полей текущей строки. Когда оператором триггера является insert все старые значения полей являются null -значениями. Аналогично, когда оператором триггера является delete все новые значения являются соответственно null -значениями.

В предыдущих версиях ORACLE обновляемыми были лишь те представления, которые соответствовали основному правилу обновления представлений. В 8-й версии ORACLE любое представление, для которого создан триггер типа instead of. Детальное описание триггеров instead of приведено в [3].

· Варианты заданий

1. Написать триггер настроенный на операцию добавления записей в таблицу сотрудников, с фиксацией идентификатора строки и даты выполненного обновления во вспомогательной таблице базы данных.

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

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

4. Создать триггер, настроенный на ввод новых строк в таблицу с описанием сотрудников. Реакцией на добавление должна быть проверка количества работающих сотрудников в заданном отделении и занесение (путем разового обновления данных соответствующего столбца) этого количества с учетом нового сотрудника во вспомогательную таблицу. В случае превышения вычисленного значения 5 в строку с данным отделением во вспомогательной таблице следует добавить некоторое информирующее сообщение.

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

· Контрольные вопросы

1. Что такое триггер?

2. Чем отличаются триггеры от хранимых процедур и функций?

3. Какие типы триггеров вы знаете?

Поиск по сайту: