Lab9-10_SQL (1059137), страница 2
Текст из файла (страница 2)
Чтобы получить представление о работе этого триггера, мы создадим триггер DELETE, предназначенный для предотвращения удаления пользователями данных о клиентах, которые живут в штате Аризона (этот код практически не отличается от кода предотвращения удаления пользователями записей клиентов с завышенным кредитом).
-
Откройте SQL Server Management Studio и раскройте папки сервера Databases=> Sales=>Tables=> dbo.Customers.
-
Щелкните правой кнопкой на папке Triggers и выберите команду New Trigger.
-
В строке CREATE TRIGGER введите имя триггера (AzDel), в строке ON введите имя таблицы (dbo.Customers).
-
В строке AFTER оставьте DELETE (удалите Update и Insert).
-
В поле (--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
-
Для создания триггера щелкните на кнопке 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 на наличие остатков товара на складе на момент продажи.
-
Откройте SQL Server Management Studio и раскройте папки сервера Databases=> Sales=>Tables=> dbo.Products.
-
Щелкните правой кнопкой на папке Triggers и выберите команду New Trigger.
-
В строке CREATE TRIGGER введите имя триггера (CheckStock), в строке ON введите имя таблицы (dbo.Products).
-
В строке AFTER оставьте UPDATE (удалите Delete и Insert).
-
В поле (--Insert statements for trigger here) введите следующий код триггера (рис. 7).
IF (SELECT InStock from inserted) < 0
BEGIN
PRINT 'Cannot oversell Products'
PRINT 'Transaction has been cancelled'
ROLLBACK
END
Puc. 7. Создание триггера на обновление
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. В этом триггере мы запретим изменения в поле с номером телефона.
-
Откройте SQL Server Management Studio и раскройте папки сервера Databases=> Sales => Tables=> dbo. Customers.
-
Щелкните правой кнопкой на папке Triggers и выберите команду New Trigger.
-
В строке CREATE TRIGGER введите имя триггера (CheckPN), в строке ON введите имя таблицы (dbo. Customers).
Рис. 9. Создание триггера на изменение одного столбца
4
. В строке AFTER оставьте UPDATE (удалите Delete и Insert).
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.
Триггеры INSTEAD OF
Представления можно применять для отображения нескольких столбцов таблицы, поднабора строк таблицы или данных из нескольких таблиц. Они удобны для просмотра данных, однако при модификации данных через представление могут возникнуть проблемы.
Поскольку представления могут не отображать все столбцы таблицы, инструкции модификации данных могут не выполняться. Предположим, что у нас имеется таблица клиентов с такой информацией, как фамилия, адрес, город, штат, почтовый индекс и т.д. Затем представим, что мы создали представление, отображающее все столбцы, за исключением поля city. Если вы попытаетесь обновить таблицу клиентов через это новое представление, то обновление не будет выполнено, поскольку поле city (а оно обязательно) недоступно через представление. Использование триггера INSTEAD OF может сделать такой тип обновлений успешным.
В следующей последовательности действий мы создадим триггер INSTEAD OF, который будет вставлять в таблицу значение, недоступное в представлении. Для этого мы вначале создадим представление, не отображающее столбец city (обязательный для обновления), а затем попытаемся выполнить обновление этого столбца. Далее мы создадим триггер INSTEAD OF, который может вставлять отсутствующее значение, и вновь попытаемся выполнить обновление.
![](https://s.studizba.com/z.php?f=/templates/si/i/noavatar.png&w=100&h=100&t=1"){kind=avatar}
