Пример триггера.

Рассмотрим простой пример триггера, который срабатывает до вставки данных в таблицу. В качестве полигона для испытаний будем использовать все ту же БД колледжа, информационно-логическая модель которой была представлена в разделе 5.8. Наш триггер будет срабатывать при добавлении новой строки в таблицу “students” и проверять, есть ли информация о новом студенте в таблице “stud_info” и существует ли группа, в которую он зачисляется. Для создания триггера будем использовать диалект Transact-SQL.

CREATE TRIGGER trig_newStud

ON students FOR INSERT

AS

IF (SELECT COUNT(*) FROM stud_info, inserted

WHERE stud_info.id = inserted.id) < 1

BEGIN

ROLLBACK TRANSACTION

PRINT ‘Can not insert unknown student’

RAISERROR 23456

END

ELSE

IF (SELECT COUNT(*) FROM groups, inserted

WHERE groups.id = inserted.group_id) < 1

BEGIN

ROLLBACK TRANSACTION

PRINT ‘Can not add student to unknown group’

RAISERROR 54126

END;

Рассмотрим поподробнее исходный текст созданного триггера. В первой строке указывается ключевое слово CREATE TRIGGER, сообщающее СУБД о том, что будет создан триггер, и его имя. На следующей строке указывается таблица, к которой будет привязан создаваемый триггер, и событие, при котором он будет срабатывать (в нашем случае триггер привязан к таблице “students” и срабатывает при инициализации инструкции INSERT). После ключевого слова AS начинается непосредственно код триггера (его тело).

Сначала в теле триггера проверяется, есть ли в таблице “stud_info” строка, соответствующая добавляемому студенту. Если такой строки нет – происходит откат изменений, сделанных командой INSERT, выводится сообщение для пользователя (команда PRINT) и возбуждается исключительная ситуация (команда RAISERROR, в которую передается код ошибки, он же код выхода), которая производит выход из триггера. В противном случае, если такая строка есть, - триггер проверяет, существует ли группа, в которую зачисляется новый студент.

Для проверки существования нужных строк создается внутреннее соединение соответствующей (проверяемой) таблицы и виртуальной таблицы INSERTED, создаваемой СУБД на время работы триггера (виртуальные таблицы для триггеров будут рассмотрены ниже). Соединение производится по ключевому полю проверяемой таблицы для обеспечения однозначности результата (т.к. первичный ключ всегда содержит уникальные значения). Затем с помощью агрегатной функции подсчитывается количество строк результата – если меньше одной, значит нужной строки не существует.

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

CREATE TRIGGER trig_mathReExam

ON math_exam FOR UPDATE

AS

IF UPDATE(ball)

BEGIN

UPDATE math_exam

SET edate = GETDATE

WHERE math_exam.id = inserted.id

END

ELSE

BEGIN

ROLLBACK TRANSACTION

PRINT ‘Can not update table – query is false)

RAISERROR 57125

END;

В данном триггере использована еще одна возможность MS SQL Server для работы с триггерами – возможность выяснить, какие именно столбцы были изменены. Для этого в триггере используется специальная форма инструкции IF – IF UPDATE(). В нашем случае, триггер проверяет была обновлена оценка, и если да – то обновляет дату выставления этой оценки (функция GETDATE возвращает текущие дату и время). Таким образом, для обновления целой строки данных в таблице достаточно обновить значение только одного поля.