2. Создание триггеров

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

Пример 1. Создадим триггер для поддержки целостности данных – проверки наличия связанной записи в главной таблице при вводе данных в подчиненную таблицу (структура БД – см. рисунок 9) .

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

Для создания триггера с именем INSCHECK c помощью утилиты SQL Server Enterprise Manager необходимо выбрать таблицу USP в списке объектов базы данных, затем выполнить команду All tasks/Manage Triggers меню Actions. После этого будет открыто диалоговое окно свойств триггера (Trigger Properties), в которое необходимо ввести текст процедуры:

CREATE TRIGGER INSCHECK ON USP

FOR INSERT

AS

DECLARE @X

SELECT @X= Nom_Zach FROM INSERTED

IF NOT EXISTS (SELECT * FROM

STUDENTS WHERE Nom_Zach =@X)

BEGIN

ROLLBACK TRANS

RAISERROR(‘ОШИБКА ЦЕЛОСТНОСТИ!’,16,10)

END

Триггер активизируется при вставке (ключевое слово INSERT) новой записи. После определения переменной @X ей присваивается значение поля Nom_Zach добавляемой записи. В процессе использования триггера создается временная таблица INSERTED, хранящая в себе добавляемые значения начальной таблицы USP. С помощью оператора SELECT переменной @X присваивается значение поля Nom_Zach из таблицы USP, то есть значение поля Nom_Zach вновь добавляемой записи.

Следующий шаг работы триггера – проверка наличия в поле Nom_Zach таблицы STUDENTS значения переменной @X, то есть проверка допустимости вводимого значения. Если значение не найдено, то выполняется блок операторов, заключенных в области BEGIN …END. С помощью команды ROLLBACK TRANSACTION, используемой при работе с транзакциями, отменяется последняя операция. Оператор RAISERROR осуществляет выдачу системного сообщения об ошибке. Значения 16 и 10 определяют уровень критичности операции.

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

В таблице STUDENTS создадим триггер DELCHECK следующего содержания:

CREATE TRIGGER DELCHECK ON STUDENTS

FOR DELETE

AS

DECLARE @X int

SELECT @X= Nom_Zach FROM DELETED

IF EXISTS(SELECT *

FROM USP

WHERE Nom_Zach = @X)

DELETE FROM USP WHERE Nom_Zach =@X

В первой строке кода создается новый триггер с именем DELCHECK для таблицы STUDENTS, активизирующийся при удалении записи. Следующим шагом является определение переменной @X, которая будет содержать значение поля Nom_Zach удаляемой записи. Затем с помощью оператора SELECT данной переменной присваивается значение поля Nom_Zach удаляемой записи, находящейся в таблице DELETED. С помощью оператора EXISTS проверяется наличие записей в таблице USP, у которых в поле Nom_Zach находится значение @X. Если такие записи найдены, то система выполняет их удаление.

Пример 3. Триггер осуществляет проверку введенной в таблицу USP оценки.

CREATE TRIGGER USP__INPUT

ON USP

FOR INSERT, UPDATE

AS

DECLARE @Mark

SELECT @Mark = Mark FROM INSERTED

IF (@Mark<2)Or (@Mark>5)

BEGIN

RAISERROR ('оценка должна находиться в диапазоне от 2 до5’)

ROLLBACK TRANSACTION

END

Пример 4. Триггер для увеличения стипендии на 10% тем студентам, которые сдали зачет по спецкурсу (код предмета PKod=1278).

CREATE TRIGGER UPDATE_ STIP

ON USP

FOR INSERT

AS

DECLARE @X int

DECLARE @Z int

DECLARE @P int

DECLARE @Y varchar(10)

select @X= Nom_Zach From INSERTED

select @Y= Mark From INSERTED

select @Z= PKodFrom INSERTED

select @P= Nom_Zach From USP

Where Nom_Zach = @X

if(@Y= 'зачтено' AND @Z=1278)

BEGIN

UPDATE STUDENTS

SET Stip = Stip*1.1

WHERE Nom_Zach=@P

END

Следующие триггеры созданы для БД “Продажа объектов недвижимости”. Реляционная модель данных приведена на рисунке 8.

Пример 5.Создать триггер для уменьшения цены квартиры на 5%, если при добавлении записи в таблицу VIEWING в поле COMMENT занесено значение “требует ремонта”.

CREATE TRIGGER UPDATE_PRICE

ON VIEWING

FOR INSERT

AS

DECLARE @X int

DECLARE @Y varchar(50)

select @X=Property_no From INSERTED

select @Y=Comments From INSERTED

if(@Y= 'требует ремонта')

BEGIN

UPDATE PROPERTY

SET Selling_Price=Selling_Price*0.5

FROM PROPERTY

WHERE Property_no=@X

END

Область применения триггеров не ограничивается какими-то строго очерченными рамками. Триггеры можно применять по своему усмотрению, исходя из требований к удобству и производительности выполняемых действий. Не следует применять триггеры для выполнения простых проверок, которые могут быть произведены с помощью правил или ограничений целостности. Кроме того, следует избегать использования триггеров, если те же действия могут быть реализованы с помощью хранимой процедуры или обычного пакета команд Transact-SQL. Использование триггеров нежелательно еще и по той причине, что они блокируют таблицу до завершения триггера, запрещая обращение к ресурсу других пользователей.