Лекция № 11

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

План

1. Понятие триггера в СУБД

2. Работа с триггерами в SQL Server

3. Работа с триггерами в MySQL

4. Работа с триггерами в SQLite

1. Понятие триггера в СУБД

Триггер - хранимая процедура особого типа, которую пользователь не вызывает непосредственно, а исполнение которой обусловлено действием по модификации данных: добавлением INSERT, удалением DELETE строки в заданной таблице, или изменением UPDATE данных в определённом столбце заданной таблицы реляционной базы данных. Триггеры применяются для обеспечения целостности данных и реализации сложной бизнес-логики. Триггер запускается сервером автоматически при попытке изменения данных в таблице, с которой он связан.

Момент запуска триггера определяется с помощью ключевых слов BEFORE (триггер запускается до выполнения связанного с ним события: BEFORE INSERT, BEFORE UPDATE, BEFORE DELETE) или AFTER (после события: AFTER INSERT, AFTER UPDATE, AFTER DELETE). В случае, если триггер вызывается до события, он может внести изменения в модифицируемую событием запись (конечно, при условии, что событие — не удаление записи).

Кроме того, триггеры могут быть привязаны не к таблице, а к представлению (VIEW). В этом случае с их помощью реализуется механизм «обновляемого представления».

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

Также ряд серверов поддерживают строчные триггеры, которые срабатывают отдельно для каждой изменяемой строки в таблице. В этом случае для отличия табличных триггеров от строчных вводится дополнительные ключевые слова при описании строчных триггеров. Например, словосочетание FOR EACH ROW.

2. Работа с триггерами в SQL Server

При работе с триггерами в SQL Server нужно учитывать следующее:

- данная СУБД не поддерживает момент срабатывания триггера BEFORE, доступен только AFTER;

- данная СУБД может создавать только табличные триггеры, срабатывающие один раз при изменении таблицы, а не отдельно каждой строки;

- чтобы прервать выполнение триггера используют команду TRANSACTION ROLLBACK

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

Триггер создается с помощью инструкции CREATE TRIGGER, которая имеет следующий синтаксис:

CREATE TRIGGER имя_триггера

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

AFTER [INSERT] [,] [UPDATE] [,] [DELETE]

BEGIN

SET NOCOUNT ON;

код триггера

END

В параметре имя_триггера задается произвольное имя, начинающееся с буквы. Имена триггеров должны быть уникальными в пределах одной БД.

В параметре имя_таблицы задается имя таблицы, для которой создается триггер.

Параметры INSERT, UPDATE и DELETE задают действие триггера. Под действием триггера имеется в виду инструкции Transact-SQL, которые запускает триггер. Допускается любая комбинация этих трех инструкций. Инструкция DELETE не разрешается, если используется параметр IF UPDATE().

Изменение структуры триггера

Для изменения триггера используют инструкцию ALTER TRIGGER, которая имеет такую же структуру, как и CREATE TRIGGER.

ALTER TRIGGER имя_триггера

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

AFTER [INSERT] [,] [UPDATE] [,] [DELETE]

BEGIN

SET NOCOUNT ON;

код триггера

END

Для удаления триггеров в текущей базе данных применяется инструкция DROP TRIGGER.

DROP TRIGGER имя_триггера.

Использование виртуальных таблиц DELETED и INSERTED

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

- DELETED - содержит копии строк, удаленных из таблицы или строк, со старыми значениями до их изменения;

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

Структура этих таблиц эквивалентна структуре таблицы, для которой определен триггер.

Таблица DELETED используется в том случае, если в инструкции CREATE TRIGGER указывается предложение DELETE или UPDATE, а если в этой инструкции указывается предложение INSERT или UPDATE, то используется таблица INSERTED. Это означает, что для каждого запроса DELETE, выполненного в действии триггера, создается таблица DELETED. Подобным образом для каждого запроса INSERT, выполненного в действии триггера, создается таблица INSERTED.

Запрос UPDATE рассматривается, как запрос DELETE, за которым следует запрос INSERT (старая версия записи удаляется, а новая версия вставляется как новая строка). Поэтому для каждой инструкции UPDATE, выполненной в действии триггера, создается как таблица DELETED, так и таблица INSERTED (в указанной последовательности).

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

Работа с триггерами с помощью утилиты SQL Server Management Studio

Для создания триггера можно в левой части окна утилиты открыть узел нужной таблицы БД. В результате отобразится список объектов таблицы: Столбцы, Ключи, Ограничения, Триггеры, Индексы, Статистика. Все триггеры для выбранной таблицы будут находиться в папке Триггеры.

Для создания триггера в контекстном меню папки Триггеры выберите команду "Создать триггер". В результате откроется окно с шаблоном кода для ввода текста триггера.

Для корректной работы триггера рекомендуется изменять только команды, заключенный в символы "<" ">". Остальные команды лучше не удалять. Текст триггера нужно писать после команды

SET NOCOUNT ON;

После ввода текста триггера щелкните на кнопке "Выполнить", так как вы выполняете запросы. Если ошибок нет, то появится сообщение об успешном выполнении команды. Обновите папку Триггеры клавишей F5, чтобы увидеть созданный триггер.

Для изменения триггера откройте его двойным щелчком мыши, внесите изменения в текст триггера и щелкните на кнопке "Выполнить".

Для удаления триггера в его контекстном меню выберите команду "Удалить" и подтвердите операцию.

Области применения триггеров

Рассмотрим несколько областей применения триггеров.

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

- создания журнала логов действий в таблицах базы данных;

- реализации бизнес-правил;

- принудительного обеспечения ссылочной целостности.

Создание журнала логов

Часто при изменении данных в таблицах БД возникает необходимость вести журнал таких изменений. Например, в нашей БД dbase будем вести журнал изменения оценок студента. В журнал будем записывать такие данные:

- идентификатор оценки;

- идентификатор предмета;

- идентификатор студента;

- старое значение оценки;

- новое значение оценки;

- имя пользователя, который изменил эту оценку;

- дата и время изменения оценки.

Для журнала создадим отдельную таблицу с именем ArhivOcenki и следующей структурой:

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

Для решения поставленной задачи будем создавать триггер для таблицы ocenki с указанием события AFTER UPDATE, INSERT. При этом триггер должен срабатывать не просто при изменении данных в таблице ocenki, а именно при изменении данных в поле ocenka.

Код триггера имеет вид:

-- имя триггера saveocenka

create trigger saveocenka

-- имя таблицы, для которой создается триггер

on ocenki

-- события срабатывания триггера

-- в нашем случае после изменения или добавления оценки

after update, insert

begin

set nocount on;

-- код триггера

-- если изменения произошли в поле ocenka

if update(ocenka)

begin

-- описываем переменные

-- идентификатор оценки

declare @idocenka int;

-- идентификатор студента

declare @idstud int;

-- идентификатор предмета

declare @idpredmet int;

-- старая оценка до изменения

declare @ocenkaold decimal(3,0);

-- новая оценка после изменения или добавления

declare @ocenkanew decimal(3,0);

-- с помощью запросов получаем значения для наших переменных

-- все значения для переменных считываем из новой записи таблицы inserted

select @idocenka = (select id_ocenka from inserted);

select @idstud = (select id_stud from inserted);

select @idpredmet = (select id_predmet from inserted);

select @ocenkanew = (select ocenka from inserted);

-- только значение старой оценки считываем из старой записи таблицы deleted

select @ocenkaold = (select ocenka from deleted);

-- в таблицу ArhivOcenki добавляем новую запись

-- и заполняем все поля значениями переменных

-- имя пользователя получаем встроенной функцией user_name()

-- дату и время получаем встроенной функцией getdate()

insert into arhivocenki

values

(@idocenka, @idstud, @idpredmet, @ocenkaold, @ocenkanew, user_name(), getdate());

end

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

Реализация бизнес-правил

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

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

Добавим в таблицу students поле srbal как показано на рисунке:

Для таблицы ocenki создадим триггер с параметрами AFTER UPDATE, INSERT вида:

-- имя триггера calcsrbal

create trigger calcsrbal

-- имя таблицы, для которой создается триггер

on ocenki

-- события срабатывания триггера

-- в нашем случае после изменения или добавления оценки

after update, insert

begin

set nocount on;

-- код триггера

-- если изменения произошли в поле ocenka

if update(ocenka)

begin

-- описываем переменные

-- идентификатор студента

declare @idstud int;

-- средний балл

declare @srbal decimal(5,2);

-- с помощью запроса получаем идентификатор студента,

-- для которого изменена или добавлена оценка

select @idstud = (select id_stud from inserted);

--считаем средний балл студента с найденным идентификатором

select @srbal = (select AVG(ocenka) from ocenki where id_stud=@idstud);

-- в таблице students заменяем средний балл найденного студента

-- на новое значение

update students

set srbal=@srbal

where id_stud=@idstud;

end

Попробуйте изменить оценки любого студента в таблице ocenki и убедитесь, что для этого студента в таблице students автоматически обновляется значение в поле srbal.

Замечание. Обратите внимание, что для одной таблицы ocenki мы создали два триггера с параметрами AFTER UPDATE, INSERT. Оба этих триггера выполнятся при наступлении указанных событий, но порядок их выполнения не определен.

Ограничение на ввод

При вводе оценки пользователь может ввести значения от 2 до 5. Все остальные значения должны игнорироваться. Данную задачу проще всего реализовать с помощью ограничения check для поля при создании таблицы, но также можно реализовать с помощью триггеров.

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

Создадим для таблицы ocenki еще один триггер с параметром AFTER UPDATE, INSERT следующего вида:

-- имя триггера checkocenka

create trigger checkocenka

-- имя таблицы, для которой создается триггер

on ocenki

-- события срабатывания триггера

-- в нашем случае после изменения или добавления оценки

after update, insert

begin

set nocount on;

-- код триггера

-- если изменения произошли в поле ocenka

if update(ocenka)

begin

-- описываем переменную

-- новая оценка после изменения или добавления

declare @ocenkanew decimal(3,0);

-- с помощью запросов получаем значения для наших переменных

-- новую оценку считываем из новой записи таблицы inserted

select @ocenkanew = (select ocenka from inserted);

-- если новое значение оценки выходит за диапазон 2..5

if (@ocenkanew<2) or (@ocenkanew>5)

-- отменяем изменение оценки

rollback transaction

end

Подсчет количества записей

Пусть нам нужно подсчитывать количество студентов в каждой группе. Это можно сделать автоматически с помощью запроса функции COUNT(), запроса ё и группировки. Однако, как было показано выше в примере со средним баллом студентом, иногда, для скорости доступа к данным, можно хранить такое количество в отдельном поле таблицы и автоматически обновлять его при удалении, перемещении или добавлении студента.

Добавим в таблицу grupy поле kolstud как показано на рисунке:

Для таблицы students создадим триггер с параметрами AFTER UPDATE, INSERT, DELETE вида:

-- создаем триггер с именем calckolstud

create trigger calckolstud

-- для таблицы students

on students

-- события срабатывания триггера

-- в нашем случае после добавления, удаления и обновления студента

after insert,delete,update

begin

set nocount on;

-- код триггера

-- описываем переменную

-- переменная для получения номера группы удаленного студента

declare @old int;

-- переменная для получения номера группы нового студента

declare @new int;

-- работаем с новым или перемещенным студентом

-- если у студента обновилось поле id_grup (его добавили или переместили)

if update(id_grup)

-- получаем значение id_grup нового студента

select @new = (select top 1 id_grup from inserted);

-- работаем с удаленным студентом

--получаем значение id_grup удаленного студента

select @old = (select top 1 id_grup from deleted);

--если @old существует, то есть имеется удаленная запись студента

if (@old is not null)

-- в подзапросе считаем количество студентов в группе, где учился удаленный студент

-- обновляем поле kolstud в таблице grupy

-- для группы, где учился удаленный студент

update grupy

set kolstud = (select count(*) from students where id_grup = @old)

where id_grup = @old;

-- если new существует, то есть добавлена или изменена запись студента

if (@new is not null)

-- обновляем поле kolstud в таблице grupy

-- для группы, куда добавлен или перемещен новый студент

update grupy

set kolstud = (select count(*) from students where id_grup = @new)

where id_grup = @new;

end

Попробуйте изменить значение поля id_grup любого студента в таблице students и убедитесь, что в таблице grupy автоматически обновляется значение в поле kolstud.

Попробуйте добавить нового студента в таблицу students и убедитесь, что в таблице grupy автоматически обновляется значение в поле kolstud для соответствующей группы.

Попробуйте удалить студента из таблицы students и убедитесь, что в таблице grupy автоматически обновляется значение в поле kolstud для соответствующей группы.

3. Работа с триггерами в MySQL

При работе с триггерами в MySQL нужно учитывать следующее:

- данная СУБД поддерживает момент срабатывания триггера как перед событием (BEFORE), так и после (AFTER);

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

- для одной таблицы нельзя создать несколько триггеров для одного события. Триггер создается в одном экземпляре и весь код пишется в нем сразу;

- данная СУБД может создавать только триггеры, срабатывающие отдельно для каждой строки;

- чтобы прервать выполнение триггера используют команду SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Сообщение об ошибке';

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

Триггер создается с помощью инструкции CREATE TRIGGER, которая имеет следующий синтаксис:

CREATE TRIGGER имя_триггера

BEFOR/AFTER INSERT/UPDATE/DELETE

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

FOR EACH ROW

BEGIN

код триггера

END

В параметре имя_таблицы задается имя таблицы, для которой создается триггер.

Параметры INSERT, UPDATE и DELETE задают действие триггера. Под действием триггера имеется в виду инструкции SQL, которые запускает триггер. В одном триггере допускается только одна из трех инструкций.

Изменение структуры триггера

Для удаления триггеров в текущей базе данных применяется инструкция DROP TRIGGER.

DROP TRIGGER имя_триггера.

Если нужно изменить триггер, то в MySQL нет соотвествующей команды. Для этого нужно удалить триггер и создать его заново.

Использование "новых" и "старых" версий записи таблицы в триггере

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

- OLD - содержит в виде свойств копии полей, удаленной из таблицы записи или записи со "старыми" данными до их изменения. Например, old.ocenka - значение поля ocenka из удаленной записи или "старое" значение поля ocenka из измененной записи;

- NEW - содержит в виде свойств копии полей, добавленной в таблицу записи или записи с "новыми" данными после их изменения. Например, new.ocenka - значение поля ocenka из добавленной записи или "новое" значение поля ocenka из измененной записи.

Список свойст в классах OLD или NEW эквивалентен списку полей в структуре таблицы, для которой определен триггер.

Класс OLD доступен в том случае, если в инструкции CREATE TRIGGER указывается предложение DELETE или UPDATE, а если в этой инструкции указывается предложение INSERT или UPDATE, то доступен класс NEW.

Работа с триггерами с помощью утилиты SQL Manager for MySQL

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

Для создания триггера в контекстном меню папки "Триггеры" выберите команду "Новый объект: триггер...". В результате в правой части откроется окно для ввода параметров триггера.

В верхней части введите имя триггера, укажите таблицу, для которой создается триггер. С помощью переключателей укажите тип события (BEFORE или AFTER) и само событие (INSERT, UPDATE, DELETE).

Внизу между операторными скобками BEGIN...END введите текст триггера.

После ввода текста триггера щелкните на команде "Compile". Если ошибок нет, то появится сообщение об успешном выполнении команды. Обновите папку Триггеры клавишей F5, чтобы увидеть созданный триггер.

Для изменения триггера откройте его двойным щелчком мыши, внесите изменения в текст триггера и щелкните на кнопке "Compile".

Для удаления триггера в его контекстном меню выберите команду "Удалить Триггер ..." и подтвердите операцию.

Создание журнала логов

- идентификатор оценки;

- идентификатор предмета;

- идентификатор студента;

- старое значение оценки;

- новое значение оценки;

- имя пользователя, который изменил эту оценку;

- дата и время изменения оценки.

Для журнала создадим отдельную таблицу с именем ArhivOcenki и следующей структурой:

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

Как уже было сказано выше, в MySQL нет возможности создать один триггер на несколько событий, поэтому мы должны создать 3 триггера:

- при добавлении оценки (AFTER INSERT);

- при удалении оценки (AFTER DELETE);

- при изменении оценки (AFTER UPDATE).

Для создания триггера AFTER INSERT задайте его параметры вида:

После задания всех параметров щелкните на команде "Compile".

Для создания триггера AFTER DELETE задайте его параметры вида:

После задания всех параметров щелкните на команде "Compile".

Для создания триггера AFTER UPDATE нужно учитывать, что триггер должен сработать только при условии изменения поля ocenka.

Задайте параметры нового триггера вида:

После задания всех параметров щелкните на команде "Compile".

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

Реализация бизнес-правил

Добавим в таблицу students поле srbal как показано на рисунке:

Для таблицы ocenki мы уже создали ранее триггеры AFTER INSERT, AFTER DELETE, AFTER UPDATE. Так как в MySQL нельзя создавать несколько триггеров для одного события, то для решения нашей задачи мы добавим нужный код в триггеры, созданные ранее.

Двойным щелчком откройте триггер с именем SaveNewOcenka и добавьте фрагмент кода:

-- в переменную считаем средний балл студента

set @srb=(select avg(ocenka) from ocenki where id_stud=new.id_stud);

-- в таблице students для нужного студента записываем найденный средний балл

update students

set srbal=@srb

where id_stud=new.id_stud;

После задания всех параметров щелкните на команде "Compile".

Двойным щелчком откройте триггер с именем SaveDelOcenka и добавьте фрагмент кода:

-- в переменную считаем средний балл студента

set @srb=(select avg(ocenka) from ocenki where id_stud=old.id_stud);

-- в таблице students для нужного студента записываем найденный средний балл

update students

set srbal=@srb

where id_stud=old.id_stud;

После задания всех параметров щелкните на команде "Compile".

Двойным щелчком откройте триггер с именем SaveUpdOcenka и внутри оператора IF...END IF добавьте фрагмент кода:

-- в переменную считаем средний балл студента

set @srb=(select avg(ocenka) from ocenki where id_stud=new.id_stud);

-- в таблице students для нужного студента записываем найденный средний балл

update students

set srbal=@srb

where id_stud=new.id_stud;

После задания всех параметров щелкните на команде "Compile".

Ограничение на ввод

При вводе оценки пользователь может ввести значения от 2 до 5. Все остальные значения должны игнорироваться.

В данном примере покажем, как отменить изменения в таблице при нарушении заданных условий. Проверка ввода должна проводиться в типе события BEFORE. Создадим для таблицы ocenki два триггера: BEFORE INSERT, BEFORE UPDATE. Событие BEFORE DELETE не имеет смысла, так как при удалении записи оценка не изменяется.

Для создания триггера BEFORE INSERT задайте его параметры вида:

После задания всех параметров щелкните на команде "Compile".

Для создания триггера BEFORE UPDATE нужно учитывать, что триггер должен сработать только при условии изменения поля ocenka.

Задайте параметры нового триггера вида:

После задания всех параметров щелкните на команде "Compile".

Попробуйте в таблице ocenki изменить любую существующую оценку или добавить новую оценку вне диапазона 2..5 и убедитесь, что триггеры не позволяют этого сделать.

Подсчет количества записей

Пусть нам нужно подсчитывать количество студентов в каждой группе. Это можно сделать автоматически с помощью запроса функции COUNT(), запроса INNER JOIN и группировки. Однако, как было показано выше в примере со средним баллом студентом, иногда, для скорости доступа к данным, можно хранить такое количество в отдельном поле таблицы и автоматически обновлять его при удалении, перемещении или добавлении студента.

Добавим в таблицу grupy поле kolstud как показано на рисунке:

Для таблицы students создадим 3 триггера для событий AFTER UPDATE, AFTER INSERT, AFTER DELETE.

Для создания триггера AFTER INSERT задайте его параметры вида:

После задания всех параметров щелкните на команде "Compile".

Для создания триггера AFTER DELETE задайте его параметры вида:

После задания всех параметров щелкните на команде "Compile".

Для создания триггера AFTER UPDATE нужно учитывать, что триггер должен сработать только при условии изменения поля id_grup.

Задайте параметры нового триггера вида:

После задания всех параметров щелкните на команде "Compile".

4. Работа с триггерами в SQLite

В целом работа с триггерами в SQLite во многом похожа на MySQL.

При работе с триггерами в SQLite нужно учитывать следующее:

- данная СУБД поддерживает момент срабатывания триггера как перед событием (BEFORE), так и после (AFTER);

- данная СУБД поддерживает новое событие, которое указывает на обновление данных в отдельном поле (UPDATE OF имя_поля);

- данная СУБД может создавать как триггеры, срабатывающие отдельно для каждой строки (FOR EACH ROW), так и для таблицы целиком (FOR EACH STATEMENT);

- чтобы прервать выполнение триггера используют команду RAISE();

- в триггерах SQLite нельзя использовать переменные, операторы типа if...then.

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

Триггер создается с помощью инструкции CREATE TRIGGER, которая имеет следующий синтаксис:

CREATE TRIGGER имя_триггера

BEFOR/AFTER INSERT/UPDATE/DELETE/UPDATE OF

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

FOR EACH ROW

BEGIN

код триггера

END

В параметре имя_таблицы задается имя таблицы, для которой создается триггер.

Изменение структуры триггера

Для удаления триггеров в текущей базе данных применяется инструкция DROP TRIGGER.

DROP TRIGGER имя_триггера.

Если нужно изменить триггер, то в SQLite нет соответствующей команды. Для этого нужно удалить триггер и создать его заново.

Использование "новых" и "старых" версий записи таблицы в триггере

Работа с триггерами с помощью утилиты SQLite Expert

Для создания триггера можно в левой части окна утилиты открыть узел нужной БД и выбрать нужную таблицу. В правой части окна перейдите на закладку "Design", а затем на закладку "Triggers".

В результате отобразится список триггеров для выбранной таблицы. С помощью кнопки "Add" можно добьавить новый триггер, кнопки "Modify" - изменить триггер и переименовать его, кнопки "Delete" - удалить триггер.

При создании или изменении триггера откроется новое окно вида:

В поле "TRIGGER" введите имя триггера, в первом выпадающем списке выберите тип события BEFORE/AFTER, в следующем выпадающем списке выберите событие INSERT/UPDATE/DELETE/UPDATE OF. Если выбрано событие UPDATE OF, то в следующем поле можно указать имя поля, при изменении которого сработает триггер.

В нижнем выпадающем списке выберите тип срабатывания триггера: FOR EACH ROWS - срабатывает отдельно при изменении каждой записи таблицы (строчный триггер), FOR EACH STATEMENT - срабатывает один раз после внесения всех изменений в таблицу (табличный триггер).

В нижней части между словами BEGIN...END вводится текст триггера.

После задания всех параметров нажмите на кнопку "ОК", а затем на кнопку "Apply".

Создание журнала логов

- идентификатор оценки;

- идентификатор предмета;

- идентификатор студента;

- старое значение оценки;

- новое значение оценки;

- дата и время изменения оценки.

Замечание. Так как СУБД SQLite является локальной встраиваемой СУБД, то у нее нет понятия учетной записи или имени пользователя. Поэтому имя пользователя в журнал сохранять не будем.

Для журнала создадим отдельную таблицу с именем ArhivOcenki и следующей структурой:

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

Как уже было сказано выше, в SQLite нет возможности создать один триггер на несколько событий, поэтому мы должны создать 3 триггера:

- при добавлении оценки (AFTER INSERT);

- при удалении оценки (AFTER DELETE);

- при изменении оценки (AFTER UPDATE).

Для создания триггера AFTER INSERT задайте его параметры вида:

После задания всех параметров щелкните на кнопке "OK", а затем на кнопке "Apply".

Для создания триггера AFTER DELETE задайте его параметры вида:

После задания всех параметров щелкните на кнопке "OK", а затем на кнопке "Apply".

Задайте параметры нового триггера вида:

После задания всех параметров щелкните на кнопке "OK", а затем на кнопке "Apply".

Реализация бизнес-правил

Добавим в таблицу students поле srbal как показано на рисунке:

Для таблицы ocenki мы уже создали ранее триггеры AFTER INSERT, AFTER DELETE, AFTER UPDATE. В SQLite можно создавать несколько триггеров для одного события. То есть для решения нашей задачи мы можем создать новые триггеры с указанными событиями. Но мы для простоты добавим нужный код в триггеры, созданные ранее.

Двойным щелчком откройте триггер с именем SaveNewOcenka и добавьте фрагмент кода:

-- в таблице students для нужного студента записываем его средний балл

-- сам средний балл студента находим через подзапрос

update students

set srbal=(select avg(ocenka) from ocenki where id_stud=new.id_stud)

where id_stud=new.id_stud;

После задания всех параметров щелкните на кнопке "OK", а затем на кнопке "Apply".

Двойным щелчком откройте триггер с именем SaveDelOcenka и добавьте фрагмент кода:

-- в таблице students для нужного студента записываем его средний балл

-- сам средний балл студента находим через подзапрос

update students

set srbal=(select avg(ocenka) from ocenki where id_stud=old.id_stud)

where id_stud=old.id_stud;

После задания всех параметров щелкните на кнопке "OK", а затем на кнопке "Apply".

Двойным щелчком откройте триггер с именем SaveUpdOcenka и добавьте фрагмент кода:

-- в таблице students для нужного студента записываем его средний балл

-- сам средний балл студента находим через подзапрос

update students

set srbal=(select avg(ocenka) from ocenki where id_stud=new.id_stud)

where id_stud=new.id_stud;

После задания всех параметров щелкните на кнопке "OK", а затем на кнопке "Apply".

Подсчет количества записей

Добавим в таблицу grupy поле kolstud как показано на рисунке:

Для таблицы students создадим 3 триггера для событий AFTER UPDATE, AFTER INSERT, AFTER DELETE.

Для создания триггера AFTER INSERT задайте его параметры вида:

После задания всех параметров щелкните на кнопке "OK", а затем на кнопке "Apply".

Для создания триггера AFTER DELETE задайте его параметры вида:

После задания всех параметров щелкните на кнопке "OK", а затем на кнопке "Apply".

Задайте параметры нового триггера вида:

После задания всех параметров щелкните на кнопке "OK", а затем на кнопке "Apply".

Вопросы для самоконтроля.

1. Что такое триггер и для чего он используется в СУБД? Какие параметры вызова имеют триггеры?

2. Какие особенности имеют триггеры в SQL Server? Приведите команду создания триггера в SQL Server. Как изменить и удалить триггер?

3. Как обратиться к "новой" и "старой" версии записи таблицы в триггере SQL Server?

4. Как выполнить операции над триггерами в SQL Server с помощью утилиты SQL Server Management Studio?

5. Какие особенности имеют триггеры в MySQL? Приведите команду создания триггера в MySQL. Как изменить и удалить триггер?

6. Как обратиться к "новой" и "старой" версии записи таблицы в триггере MySQL?

7. Как выполнить операции над триггерами в MySQL с помощью утилиты SQL Manager for MySQL?

8. Какие особенности имеют триггеры в SQLite? Приведите команду создания триггера в SQLite. Как изменить и удалить триггер?

9. Как обратиться к "новой" и "старой" версии записи таблицы в триггере SQLite?

10. Как выполнить операции над триггерами в SQLite c помощью утилиты SQLite Expert?