- •Лабораторная работа №1: Создание баз данных
- •Лабораторная работа №2: Создание таблиц и ограничений
- •Лабораторная работа №3: Основы Transact sql: Простые (однотабличные) выборки данных
- •Запросы на выборку данных (оператор select)
- •Лабораторная работа №4: Основы Transact sql: Сложные (многотабличные запросы)
- •Подзапросы
- •Операции соединения
- •Множественные операции
- •Лабораторная работа №5: Основы Transact sql: Добавление, изменение и удаление данных в таблицах
- •Добавление новых записей
- •Удаление записей
- •Изменение данных
- •Лабораторная работа №6: Представления
- •Создание представлений в Management Studio
- •Создание представлений с помощью кода sql
- •Предложение order by и представления
- •Выполнение представлений
- •Лабораторная работа №7: Программирование на t-sql Синтаксис и соглашения t-sql
- •Переменные
- •Средства управления потоком команд. Программные конструкции
- •Лабораторная работа №8: Хранимые процедуры
- •Лабораторная работа №9: Функции Системные функции
- •Пользовательские функции
- •Лабораторная работа №10: Обработка ошибок. Управление транзакциями. Триггеры. Обработка ошибок. Блок try…catch.
- •Активация сообщений об ошибках вручную. Инструкция raiserror
- •Управление транзакциями
- •Триггеры
- •Лабораторная работа №11: Система безопасности sql Server
Триггеры
Триггер, подобно хранимой процедуре, представляет собой сохраненный на сервере набор инструкций T-SQL. Главное отличие заключается в том, что его невозможно выполнить вручную с помощью команды EXEC. Триггер вызывается на выполнение не пользователем, а прикрепляется к определенной таблице и инициируется самим сервером баз данных как отклик на события вставки, обновления и удаления данных из этой таблицы, т.е. триггер выполняется автоматически как часть самого оператора модификации данных. Триггер на вставку запускается, когда в таблицу вставляется новая запись. Триггер на удаление запускается, когда из таблицы удаляется некоторая запись. Триггер на обновление запускается, когда некоторая запись таблицы изменяется. Кроме того можно определить триггер реагирующий сразу на несколько разных типов операций модификации, например на обновление и вставку. Триггер выполняется внутри того же пространства транзакции, что и оператор модификации данных, поэтому откат транзакции в триггере отменяет и саму исходную операцию модификации данных.
Триггеры – наиболее мощный инструмент поддержания целостности базы данных, поскольку они могут выполнять любые необходимые для поддержания целостности данных действия:
-
Сравнивать предшествующие и новые версии данных. В большинстве случаев в момент выполнения триггера необходимо знать, какие изменения были выполнены исходным оператором модификации данных. Сведения об этом можно найти в таблицах inserted (вставленные) и deleted (удаленные), которые становятся доступны внутри триггера. Эти таблицы – фактически представления строк в файле регистрации транзакции, которые были изменены оператором и имеют структуры и имена столбцов, идентичные таблице, которая изменилась. Таким образом, оценить, какие именно изменения были произведены в таблице, можно исследуя содержимое таблиц inserted и deleted, как показано в следующей таблице, и соответственно предпринимать те или иные действия в зависимости от обнаруженных различий.
Оператор
Содержимое таблицы inserted
Cодержимое таблицы deleted
INSERT
Добавленные строки
Пусто
UPDATE
Новые строки
Старые строки
DELETE
Пусто
Удаленные строки
-
Осуществлять отмену недопустимых модификаций посредством отката транзакции. Это возможно благодаря тому, что триггер запускается в рамках транзакции исходной операции модификации данных.
-
Осуществлять считывание из других таблиц.
-
Изменять другие таблицы.
-
Выполнять хранимые процедуры и функции.
Как правило триггеры используют для реализации сложных ограничений целостности, которые не могут быть обработаны посредством типов данных, обычных ограничений, декларативной ссылочной целостностью, например, реализация сложных ограничений столбцов или генерирование сложных значений по умолчанию, основанных на данных в других строках или таблицах.
Потребность в триггерах возникает тогда, когда требуется автоматическая реакция базы данных на определенные действия пользователя. К примеру, когда из рабочей таблицы удаляется запись, вполне возможно, что необходимо сохранить ее в некоторой дополнительной архивной таблице для последующего аудита. Это можно осуществить, создав триггер на удаление в первой таблице. В момент удаления записи этот триггер будет вызван на выполнение. В это время он еще будет иметь доступ ко всем удаляемым данным и сможет их скопировать в какое-либо другое место.
Триггеры также используют в более сложной и гибкой форме ограничений. Ограничения лимитированы только пределами одной таблицы, в то время как триггеры потенциально имеют доступ ко всей базе данных. Предположим, что необходимо разрешить принимать заказы только от тех клиентов, которые не имеют задолженностей по уже имеющимся счетам. В этом случае можно создать триггер на вставку, который проверяет данное правило и в случае необходимости отменяет исходную операцию вставки (откатывает транзакцию) с выбросом соответствующего сообщения об ошибке.
Общий синтаксис запроса на создания триггера выглядит следующим образом:
CREATE TRIGGER имя_триггера ON имя_таблицы AFTER { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } AS { инструкции T-SQL }
При создании триггера указывается его имя, имя таблицы, для которой он определяется, и события, при возникновении которых он должен выполняться (перечисление после ключевого слова AFTER). Эти события соответствуют трем операциям модификации данных: вставка, обновление и удаление. При этом триггер запускается один раз на каждую операцию модификации данных вне зависимости от количества затронутых этой операцией записей.
Определим в таблице City триггер, отслеживающий все изменения в данной таблице. Для хранения информации обо всех операциях модификации, выполняемых над данными в этой таблице, создадим специальную таблицу sysCityAudit со следующими столбцами: IdOperation (уникальный идентификатор операции), TypeOp (тип операции: вставка, обновление или удаление), IdCity, CityName, DateAndTime (дата и время выполнения операции), UserName (имя пользователя, изменившего данные). Запрос на создание данной таблицы приведен ниже:
CREATE TABLE [dbo].[sysCityAudit](
[IdOperation] [int] IDENTITY(1,1) NOT NULL,
[TypeOp] [varchar](50) NOT NULL,
[IdCity] [int] NOT NULL,
[CityName] [nvarchar](20) NULL,
[DateAndTime] [datetime] NOT NULL CONSTRAINT [DF_sysCityAudit_DateAndTime] DEFAULT (getdate()),
[UserName] [nvarchar](256) NOT NULL CONSTRAINT [DF_sysCityAudit_UserName] DEFAULT (user_name()),
CONSTRAINT [PK_sysCityAudit] PRIMARY KEY CLUSTERED
(
[IdOperation] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Запрос на создание триггера, отслеживающего все изменения в таблице City:
CREATE TRIGGER [dbo].[tr_CityAudit] ON [dbo].[City]
AFTER INSERT,DELETE,UPDATE
AS
BEGIN
SET NOCOUNT ON;
IF EXISTS(SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted)
INSERT sysCityAudit(TypeOp,IdCity,CityName)
SELECT 'Обновление', IdCity, CityName
FROM inserted
ELSE IF EXISTS(SELECT * FROM inserted) --
INSERT sysCityAudit(TypeOp,IdCity,CityName)
SELECT 'Вставка', IdCity, CityName
FROM inserted
ELSE
INSERT sysCityAudit(TypeOp,IdCity,CityName)
SELECT 'Удаление', IdCity, CityName
FROM deleted
END
Проверьте работоспособность вновь созданного триггера. Для этого произведите в таблице City различные изменения и убедитесь, что подробная информация о них была записана в таблицу sysCityAudit.
Для демонстрации применения триггера, в качестве инструмента ограничивающего возможные операции с данными в таблице в соответствии с определенными бизнес-правилами, создадим триггер, запрещающий оформлять заказы от клиентов из Москвы (к базе данных Sales сложно сформулировать осмысленное бизнес-правило, требующее использование триггера, поэтому пример весьма искусственный).
CREATE TRIGGER [dbo].[tr_OrderConstraint] ON [dbo].[Order]
AFTER INSERT,UPDATE
AS
BEGIN
SET NOCOUNT ON;
IF EXISTS(SELECT *
FROM inserted AS i INNER JOIN
Customer AS cust ON i.IdCust = cust.IdCust INNER JOIN
City AS c ON cust.IdCity = c.IdCity
WHERE c.CityName = N'Москва')
BEGIN
ROLLBACK TRANSACTION
RAISERROR('Оформление заказов для клиентов из Москвы запрещено',16,1)
END
END
Поскольку триггер выполняется в рамках транзакции соответствующей ей операции модификации данных для отмены самой операции достаточно выполнить команду ROLLBACK TRANSACTION. Кроме того в случае попытки нарушения заданного бизнес-правила желательно с помощью команды RAISEERROR отправить пользователю сообщение об ошибке c ее подробным описание.
Задание для самостоятельной работы: Создайте триггер, запрещающий добавлять в заказ товары, отсутствующие на складе.