Программирование триггера

При выполнении команд добавления, изменения и удаления записей сервер создает две специальные таблицы: inserted и deleted. В них содержатся списки строк, которые будут вставлены или удалены по завершении транзакции. Структура таблиц inserted и deleted идентична структуре таблиц, для которой определяется триггер. Для каждого триггера создается свой комплект таблиц inserted и deleted, поэтому никакой другой триггер не сможет получить к ним доступ. В зависимости от типа операции, вызвавшей выполнение триггера, содержимое таблиц inserted и deleted может быть разным:

• команда INSERT – в таблице inserted содержатся все строки, которые пользователь пытается вставить в таблицу; в таблице deleted не будет ни одной строки; после завершения триггера все строки из таблицы inserted переместятся в исходную таблицу;
• команда DELETE – в таблице deleted будут содержаться все строки, которые пользователь попытается удалить; триггер может проверить каждую строку и определить, разрешено ли ее удаление; в таблице inserted не окажется ни одной строки;
• команда UPDATE – при ее выполнении в таблице deleted находятся старые значения строк, которые будут удалены при успешном завершении триггера. Новые значения строк содержатся в таблице inserted. Эти строки добавятся в исходную таблицу после успешного выполнения триггера.

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

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

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

Еще в SQL Server 2000 появился новый тип триггеров INSTEAD OF. Данный тип триггеров предназначен в первую очередь для представлений (VIEW). Создадим триггер INSTEAD OF INSERT и внесем изменения в таблицу:

CREATE TRIGGER ins_str_view ON View_Predmet

INSTEAD OF INSERT

AS

print 'Триггер INSTEAD OF'

insert INTO View_Predmet values ('Иванов')

При выполнении вставки строки никаких изменений в таблице не происходит. Хотя и выдается сообщение о том, что строка добавлена. В таком типе триггеров необходимо добавлять код на изменение данных в таблице.

Обратите внимание на SET NOCOUNT ON в начале тела триггера. Это нужно для того, чтобы команды из тела триггера не возвращали информацию о количестве обработанных строк, и не перебивали тем самым информацию о количестве измененных строк.

CREATE TRIGGER TriggerInsteadMaster1 ON Predmet

INSTEAD OF INSERT

AS

SET NOCOUNT ON

………………………

Для получения списка столбцов, измененных при выполнении команд INSERT или UPDATE, вызвавших выполнение триггера, можно использовать функцию COLUMNS_UPDATED(). Она возвращает двоичное число, каждый бит которого, начиная с младшего, соответствует одному столбцу таблицы (в порядке следования столбцов при создании таблицы). Если бит установлен в значение «1», то соответствующий столбец был изменен. Кроме того, факт изменения столбца определяет и функция UPDATE (имя_столбца). Даже если данные до изменения и после изменения были одни и те же, эти функции покажут, что данные в поле были изменены. Например, функция UPDATE(Field) возвратит True при выполнении запроса:

Для удаления триггера используется команда

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

Приведем примеры использования триггеров.

1) Ограничить ввод данных определенным количеством, например, ограничить количество пересдач тремя.

CREATE TRIGGER ins_str ON [dbo].[Ekzamen]

FOR INSERT, UPDATE

AS

declare @a char(30),

@b datetime

IF EXISTS

(SELECT COUNT(a.predmet)

FROM ekzamen A, INSERTED B

WHERE A.fio = B.fio and A.predmet = B.predmet

GROUP BY a.predmet

HAVING COUNT(a.predmet) > 3)

begin

SELECT @a = predmet, @b = data FROM INSERTED

DELETE FROM ekzamen WHERE predmet = @a AND data = @b

end

(ЕЩЕ ВАРИАНТ)

ALTER TRIGGER ins_str ON [dbo].[Ekzamen]

FOR INSERT, UPDATE

AS

DECLARE @a char(50),

@b datetime,

@c int

SELECT @c=COUNT(a.predmet)

FROM ekzamen A, INSERTED B

WHERE A.fio = B.fio and A.predmet = B.predmet

GROUP BY a.predmet

HAVING COUNT(a.predmet)> 3

IF @c> 3

BEGIN

SELECT @a = predmet, @b = data FROM INSERTED

DELETE FROM ekzamen WHERE predmet = @a AND data = @b

END

(ЕЩЕ ВАРИАНТ)

вместо

SELECT @b = data, @c = predmet FROM INSERTED

DELETE FROM ekzamen WHERE data = @b AND predmet = @c

можно использовать

ROLLBACK TRANSACTION

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

CREATE TRIGGER [no_ins_4] ON [dbo].[Ekzamen]

FOR INSERT, UPDATE

AS

delete from Ekzamen

where fio IN (select a.fio from Ekzamen a inner join INSERTED b on a.fio=b.fio /*можно без соединения – т.к. ср. балл считается в таблице Ekzamen*/

group by a.fio

having AVG(a.ocenka) > 4)

Пример не работает т.к. проверка осуществляется до заполнения данных из таблицы INSERTED

Не будет работать по тем же причинам также пример вида

CREATE TRIGGER [no_ins_4] ON [dbo].[Ekzamen]

FOR INSERT, UPDATE

AS

if exists (select fio from ekzamen

group by fio

having avg(ocenka) > 4)

rollback tranSACTION

Пример заполнения таблицы Ekzamen

begin transaction

insert into ekzamen

values ('Иванов', 'История', '05/01/01', 2)

insert into ekzamen

values ('Петров', 'История', '05/01/01', 5)

commit

select * from E Ekzamen – Просмотр результата

СУБД для специальности СП, III курс