SELECT prodid, instock

FROM Products

2. Чтобы сработал триггер INSERT, вам нужно вставить в таблицу Orders новую запись. Для этого используйте команду Query=> New Query и выполните следующий код, согласно которому вы продаете сегодня (для получения сегодняшней даты используется функция GETDATE ()) 15 единиц товара под номером 7 клиенту с идентификатором 1:

USE Sales

INSERT Orders

VALUES (1,7,15,getdate())

Рис. 2. Проверка изначального состояния таблицы

3. Чтобы проверить срабатывание триггера INSERT и удаление 15 единиц товара
из столбца instock таблицы Products, щелкните на кнопке New Query и выполните следующий код:

USE Sales

SELECT prodid, instock

FROM Products

4. Обратите внимание на то, что из общего количества единиц в столбце instock
для товара с идентификатором 7 было вычтено количество единиц (15), про­данных клиенту 1. Теперь там содержится значение 185, а не 200 (рис 3).

Рис. 3. Количество товаров на складе уменьшено

5. Закройте окна запросов.

Таким образом мы создали триггер INSERT, ссылающийся на логическую таблицу inserted. Теперь при вставке новой записи в таблицу Orders соответствующая за­пись в таблице Products будет обновлена путем вычитания количества проданных единиц из общего количества единиц товара в столбце instock.

Триггеры DELETE

Обычно, когда пользователь выполняет инструкцию DELETE, программа SQL Server удаляет запись из таблицы, и о ней больше никогда никто не слышит. Это по­ведение изменяется при добавлении в таблицу триггера DELETE. При наличии триггера DELETE SQL Server переносит удаляемую запись в логическую таблицу в памяти с именем deleted. Таким образом, записи не исчезают полностью, и вы можете ссылаться на них в коде. Это удобно применять в бизнес-логике.

Специальную таблицу deleted можно сравнить с корзиной в операционной системе Windlows, в которую переносятся файлы, удаляемые из системы. Основное отличие заключается в том. что после выполнения транзакции таблица deleted автоматически очищается от записей, а корзину требуется очищать вручную.

Предположим, что вы не хотите, чтобы пользователи удаляли записи о клиентах с лимитом кредитования в компании, превышающим 10000 долларов. Без размещенно­го триггера DELETE пользователь может успешно удалить любую запись, независимо от кредита клиента. Если же задействовать триггер DELETE, то SQL Server поместит удаляемую запись в таблицу deleted, и вы сможете ссылаться на столбец с лимитом кредитования и обосновать успех транзакции на содержащемся в нем значении.

Чтобы получить представление о работе этого триггера, мы создадим триггер DELETE, предназначенный для предотвращения удаления пользователями данных о клиентах, которые живут в штате Аризона (этот код практически не отличается от кода предотвращения удаления пользователями записей клиентов с завышенным кредитом).

1. Откройте SQL Server Management Studio и раскройте папки сервера Databases=> Sales=>Tables=> dbo.Orders.

2. Щелкните правой кнопкой на папке Triggers и выберите команду New Trigger.

3. В строке CREATE TRIGGER введите имя триггера (AzDel), в строке ON введите имя таблицы (dbo.Orders).

4. В строке AFTER оставьте DELETE (удалите Update и Insert).

5. В поле (--Insert statements for trigger here) введите следующий код триггера (рис. 4).

IF (SELECT state FROM deleted) = 'AZ'

BEGIN

PRINT 'Cannot remove customers from AZ'

PRINT 'Transaction has cancelled'

ROLLBACK

END

6. Для создания триггера щелкните на кнопке Execute.

Рис. 4. Создание триггера на удаление

Теперь, чтобы протестировать триггер, мы попытаемся удалить запись клиента, живущего в Аризоне.

1. Откройте новый запрос SQL Server и выполните следующий код проверки наличия клиентов из Аризоны (в частности, клиент Shane Travis должен жить именно там) (рис. 5):

USE Sales

SELECT * FROM customers

Рис. 5. Проверка состава клиентов из Аризоны

2. Чтобы триггер сработал, попытайтесь удалить запись о клиенте Shane Travis из таблицы клиентов. Для этого используйте команду New Query меню Query и выполните следующий код (он должен выдать сооб­щение об ошибке) (рис. 6):

USE Sales

DELETE from customers

WHERE lname = 'Travis'

Рис. 6. Получено сообщение о блокировке операции триггером

3. Дабы убедиться в том, что запись о клиенте Shane Travis не была удалена, введи­те и выполните следующий код (в результатах вы должны увидеть эту запись):

USE Sales

SELECT * FROM customers

4. Закройте окно запросов.

Таким образом мы создали триггер DELETE, который использует логическую та­блицу deleted, чтобы проверить, не пытаетесь ли вы удалить данные о клиенте из Аризоны. Если вы пытаетесь удалить такие данные, то получите отказ в виде сообще­ния об ошибке (генерируемого инструкцией PRINT, введенной в код триггера).

Теперь, после изучения внутренней работы триггеров INSERT и DELETE, вам будет легче понять принцип действия триггеров на обновление (UPDATE).

Триггеры UPDATE

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

Метод, используемый триггером UPDATE, представляет комбинацию методов, применяемых триггерами INSERT и DELETE. Помните, что триггер INSERT использует таблицу inserted, а триггер DELETE— таблицу deleted.

Триггер UPDATE использует обе эти таблицы. Дело в том, что действие UPDATE представляет собой два отдельных действия — удаление и вставку. Вначале удаляются существующие данные, а затем вставляются новые. Таким образом, при модифика­ции пользователем данные полностью удаляются и заменяются.

Мы воспользуемся преимуществами триггера UPDATE, чтобы предотвратить продажу товаров сверх запасов. Сейчас пользователи могут делать заказы, даже если столбец instock таблицы Products будет принимать отрица­тельные значения. Мы создадим триггер, который будет проверять столбец instock таблицы Products на наличие остатков товара на складе на момент продажи.

1. Откройте SQL Server Management Studio и раскройте папки сервера Databases=> Sales=>Tables=> dbo.Products.

2. Щелкните правой кнопкой на папке Triggers и выберите команду New Trigger.

3. В строке CREATE TRIGGER введите имя триггера (CheckStock), в строке ON введите имя таблицы (dbo.Products).

4. В строке AFTER оставьте UPDATE (удалите Delete и Insert).

5. В поле (--Insert statements for trigger here) введите следующий код триггера (рис. 7).

IF (SELECT InStock from inserted) < 0

BEGIN

PRINT 'Cannot oversell Products'

PRINT 'Transaction has been cancelled'

ROLLBACK

END

6. Для создания триггера щелкните на кнопке Execute.

Итак, мы создали триггер UPDATE и можем протестировать его, попытавшись продать один из товаров сверх запаса. Попытаемся непосредственно обновить одну из записей таблицы Products.

1. Откройте новый запрос SQL Server и выполните следующий код проверки ко­личества единиц доступного продукта (для продукта с идентификатором 8 зна­чение должно быть равно 545).

USE Sales

SELECT prodid, instock FROM Products

2. Чтобы сработал триггер UPDATE, мы попытаемся продать клиенту 600 единиц товара с идентификатором 8 (шерстяные одеяла). Откройте новый запрос SQL Server и выполните следующий код (он должен выдать сообщение об ошибке (рис. 8).

USE Sales

UPDATE Products

SET InStock = (Instock - 600)

WHERE prodid = 8

Рис. 8. Попытка продать товар сверх запасов вызвала ошибку

3. Чтобы удостовериться в отмене транзакции и наличии 545 шерстяных одеял
на складе, щелкните на кнопке New Query и выполните следующий код (вы
должны увидеть 545 единиц товара с идентификатором 8):

USE Sales

SELECT prodid, instock FROM Products

4. Закройте окно запросов.

Итак, мы создали триггер UPDATE, ссылающийся на таблицу inserted, для про­верки того, не пытаетесь ли вы вставить отрицательное значение. Вам нужно прове­рять только таблицу inserted, поскольку SQL Server выполняет все математические операции перед вставкой данных. В нашем случае перед вставкой данных в таблицу программа отняла значение 600 (новое значение) от 545 (существующее значение). Это означает, что таблица inserted всегда содержит новое значение, которое тре­буется проверять. Триггеры UPDATE представляют собой весьма мощное средство, однако их еще более удобно использовать с инструкцией IF UPDATE, которая приме­няется для проверки обновлений для отдельного столбца.

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

В качестве хорошего примера можно привести базы данных кадров с такой информацией о служащих, как фамилии, адреса, оклады и идентификационные коды. Большая часть этой информации со временем изменяется, но идентификационный код выдается на всю жизнь, и не должен меняться ни по каким причинам (если, конечно, он не был введен с ошибкой изначально). Инструкцию IF UPDATE можно использовать для проверки модификации этого столбца и отмены таких изменений.

В качестве примера мы создадим триггер UPDATE с использованием инструкции IF UPDATE. В этом триггере мы запретим изменения в поле с номером телефона.

1. Откройте SQL Server Management Studio и раскройте папки сервера Databases=> Sales => Tables=> dbo. Customers.

2. Щелкните правой кнопкой на папке Triggers и выберите команду New Trigger.

3. В строке CREATE TRIGGER введите имя триггера (CheckPN), в строке ON введите имя таблицы (dbo. Customers).

5. В поле (--Insert statements for trigger here) введите следующий код триггера (рис. 9).

IF UPDATE (phone)

BEGIN

PRINT 'Cannot change phone numbers'

PRINT 'Transaction has been cancelled'

ROLLBACK

END

6. Для создания триггера щелкните на кнопке Execute.

Теперь мы протестируем триггер IF UPDATE. Чтобы он сработал, мы попытаемся обновить номер телефона клиента.

1. Откройте новый запрос SQL Server и выполните следующий код проверки телефонных номеров в таблице Customers (номер телефона клиента Tom Smith должен быть 5105551212).

USE Sales

SELECT fname, lname, phone FROM customers

2. Чтобы сработал триггер UPDATE, мы попытаемся модифицировать номер телефона клиента Тот Smith. Откройте новый запрос SQL Server и выполните следующий код (вы должны получить сообщение об ошибке).

USE Sales

UPDATE customers

SET phone = '8881234567'

WHERE lname = ‘Smith'

3. Чтобы проверить отмену транзакции, введите и выполните следующий код.

USE Sales

SELECT fname, lname, phone FROM customers

4. Закройте окно запросов.

Примечание. Инструкцию if update можно использовать в триггерах insert и update. He пытайтесь использовать ее в триггере delete, поскольку при удалении столбцы не изменяются.

Итак, мы указали SQL Server проверять модификации в указанном столб­це. Все попытки изменить телефонный номер будут безуспешными. Инструкцию IF UPDATE можно использовать для решения многих задач — все зависит от вашего во­ображения.

Другим, не менее полезным триггером является триггер замены операции INSTEAD OF.