Триггеры
1. Триггеры
2. Назначение триггеров
Триггер – это инструмент SQL-сервера, используемый для поддержания целостности данных в базе. Триггеры представляют собой специальный класс хранимых процедур, автоматически запускаемых при добавлении, изменении и удалении данных из таблицы. Каждый триггер привязывается к конкретной таблице. Когда пользователь пытается изменить данные в таблице, сервер автоматически запускает триггер, и только при его успешном завершении разрешается выполнение изменений. Триггеры применяются для обеспечения целостности данных и выполнения бизнес-правил, слишком сложных для реализации ограничений.
В отличие от хранимых процедур, триггеры нельзя вызывать напрямую, в них нельзя передавать параметры. Главное их преимущество в том, что они могут содержать сложную логику обработки. С помощью триггеров осуществляются каскадные изменения данных, что позволяет сократить объем кода для обновления данных в связанных таблицах и обеспечить синхронность изменений во всех таблицах.
Триггеры могут использоваться для выдачи пользовательских сообщений об ошибках при возникновении определенных условий в процессе выполнения этого триггера. Ограничения, правила и значения по умолчанию позволяют выводить лишь системные сообщения об ошибках.
Триггеры не возвращают наборы результатов. Это связано с тем, что операторы INSERT, UPDATE и DELETE не должны возвращать наборы результатов. Как и хранимые процедуры, триггеры содержат операторы языка Transact-SQL.
В зависимости от выполняемых пользователем действий, приводящих к запуску триггера, они делятся на три категории:
· триггеры изменения (запускаются при попытке изменения данных с помощью команды UPDATE);
· триггеры вставки (запускаются при попытке вставки данных с помощью команды INSERT);
Рекомендуемые материалы
· триггеры удаления (запускаются при попытке удаления данных с помощью команды DELETE).
При работе с триггерами доступны две специальные таблицы: таблиц вставок (INSERTED) и таблица удалений (DELETED) со структурой идентичной структуре таблицы, с которой связан триггер.
При выполнении операции DELETE строки, удаленные из модифицируемой таблицы помещаются в таблицу DELETED. При выполнении операции INSERT строки, добавленные в модифицируемую таблицу, помещаются в таблицу INSERTED. При выполнении операции UPDATE исходное значение строки для каждой измененной строки ее исходное значение помещается в таблицу DELETED, а новое значение – в таблицу INSERTED. Данные таблиц INSERTED и DELETED можно использовать в триггере.
3. Создание триггеров
Для создания триггеров используется оператор 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. Использование триггеров нежелательно еще и по той причине, что они блокируют таблицу до завершения триггера, запрещая обращение к ресурсу других пользователей.
4. Удаление триггера
После того как триггер стал ненужным или даже мешает работе, его следует удалить. Выполнение этой операции осуществляется с помощью команды DROP TRIGGER, имеющей следующий синтаксис:
DROP TRIGGER {имя триггера} [,...n]
Так как имя триггера уникально в пределах базы данных, то указание имени таблицы не требуется. С помощью одной команды DROP TRIGGER можно удалить несколько триггеров, перечислив их имена через запятую.
5. КОНТРОЛЬНЫЕ ВОПРОСЫ
1. С какой целью создаются триггеры?
2. Как осуществляется запуск триггеров?
3. Могут ли триггеры возвращать наборы результатов?
4. Какие категории триггеров существуют?
Лекция "Последствия радиационных аварий и катастроф" также может быть Вам полезна.
5. Для чего служат таблицы INSERTED и DELETED? Как эти таблицы используются при выполнении операции обновления?
6. Какой оператор служит для создания триггеров?
7. Объясните работу триггеров, приведенных в примерах.
8. Следует ли создавать триггеры в тех случаях, когда проверка может быть выполнена с помощью ограничений или правил?
9. Какой оператор служит для удаления триггера?
10. Можно ли удалить одновременно несколько триггеров?
