5.7. Пример оформления главы 5 «Создание триггеров»

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

Общий вид команды создания и модификации триггера:

CREATE TRIGGER [owner.]имя_триггера ON [owner.]имя_таблицы [ WITH ENCRYPTION ]     FOR [ AFTER | INSTEAD OF] INSERT | UPDATE |DELETE         AS         [IF UPDATE ( имя_поля )             [ AND | OR UPDATE (имя_поля) ]                 [ ...n ]         sql_statement [ ...n ] Задание 1. Создать триггер, который бы перед добавлением новой записи в таблицу, содержащую сведения о комплектующих деталях для каждого вида изделия, проверял бы правильность ввода кода изделия и кода детали и выдавал ошибку с соответствующим текстом (например, «нет изделия с кодом 199»).

Текст триггера:

CREATE trigger izd_det_insert

on izd_det for insert

as

declare @kod_i int, @kod_d int,@str char(30)

set @kod_i=(select kod_i from inserted)

set @kod_d=(select kod_d from inserted)

if not exists (select kod_i from izdel

where kod_i=@kod_i)

begin

/*создаем строку для вывода текста ошибки,

функция CONVERT преобразует число в строку

символов длиной в три символа */

set @str='Нет изделия с кодом'+convert(char(3),@kod_i)

raiserror (@str,10,1)

rollback tran

end

else

if not exists(select *

from detal where kod_d=@kod_d )

begin

set @str='Нет детали с кодом'+convert(char(3),@kod_d)

raiserror (@str,10,1)

rollback tran

end

else

commit tran

Чтобы триггер мог работать, нам пришлось предварительно отключить констрейнты типа Foreign Key для таблицы izd_ det(см. раздел 5.3.2). Они, в принципе, выполняют те же проверки, которые будет делать данный триггер, но сработают раньше триггера. Поэтому вначале просмотрим сведения о таблице izd_ det с помощью команды sp_help izd_det, чтобы узнать имена констрейнтов, а затем выполним команды изменения структуры таблицы:

SP_HELP izd_det

ALTER TABLE izd_det NOCHECK CONSTRAINT fk_id_izd

ALTER TABLE izd_det NOCHECK CONSTRAINT fk_id_det

Для проверки работы триггера выполним команду добавления записи с кодом изделия (199), которsq отсутствует в таблице izdel.

INSERT INTO izd_det VALUES(199,120,2)

Результат показан на рис. 5.26

Р ис. 5.26. Результат срабатывания триггера при попытке добавить запись с неправильным кодом изделия Затем была проведена проверка работы триггера при добавлении записи с кодом детали (202), который отсутствует в таблице detal (рис. 5.27). Команда добавления записи: INSERT INTO izd_det VALUES (120,202,2)

Рис. 5.27. Проверка срабатывания триггера при попытке ввести запись с несуществующим кодом детали

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

Команды для создания таблицы:

create table sklad (kod_i int ,kol int)

insert into sklad select kod_i,sum (kol_v) from vypusk

group by kod_i

select * from sklad

Результат показан на рис.. 5.28.

Рис. 5.28. Создание таблицы sklad

Текст триггера:

create trigger vyp_insert on vypusk

for insert

as

begin

declare @kod_i int, @s char(30), @kol_v int

set @kod_i=(select kod_i from inserted)

if not exists(select * from izdel where kod_i=@kod_i)

begin

set @s='Неправильный код изделия'+ convert(char(4),@kod_i)

raiserror(@s,10,1)

rollback tran

end

else

begin

update sklad set kol=kol+ (select kol_v from inserted)

where kod_i=@kod_i

commit tran

end

end

Результаты проверки триггера с ошибочным и правильным кодами изделия в командах добавления записи приведены соответственно на рис. 5.29, 5.30.

Р ис. 5.29. Триггер выдает сообщение об ошибке при попытке ввести код изделия, отсутствующий в таблице izdel

Р ис. 5.30. Результат срабатывания триггера при добавлении записи с правильным кодом изделия Сравнение данных таблицы sklad на рис. 5.28 и 5.30 показывает, что при вводе записи в таблицу vypusk с кодом изделия, равным 10, количеством выпущенных изделий, равным 30, в первой записи таблицы Sklad поле kol увеличилось на 30 единиц, что доказывает правильность функционирования триггера.

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