Lab9-10_SQL (Лабораторная №9-10)

Задание 9-10

Использование триггеров

1. Подключитесь к БД Sales.

2. Cоздайте и протестируйте триггер INSERT для таблицы Orders

3. Cоздайте и протестируйте триггер DELETE для таблицы Orders.

4. Cоздайте и протестируйте триггер UPDATE для таблицы Products.

5. Cоздайте и протестируйте триггер UPDATE для запрещения обновления какого-либо столбца таблицы.

6. Используйте триггер INSTEAD OF для обновления таблицы.

7. Создайте и протестируйте триггер DDL, который предотвра­тит удаление или изменение таблицы в базе данных Sales.

8. Cохраните БД для дальнейшей работы

Описание работы

Что такое триггеры

Триггер представляет собой набор инструкций SQL Server, которая выглядит и действует подобно хранимой процедуре. Единственное реальное отличие состоит в том, что триггер нельзя вызвать с помощью команды EXEC. Триггеры активизируются при выполнении пользователем определенной инструкции Transact-SQL. Триггеры DML запускаются с помощью инструкций INSERT, UPDATE или DELETE, а триггеры DDL— с помощью инструкций CREATE, ALTER и/или DROP.

Предположим, в таблице клиен­тов вы определили триггер INSERT, указывающий, что пользователи не могут добав­лять записи о новых клиентах, находящихся за пределами США. Как только пользователь попытается добавить новую запись, будет запущен триггер INSERT, который определит, соответствует ли она данному критерию. В случае соответствия запись вставляется в таблицу; в противном случае этого не происходит.

SQL Server может блокировать модификацию данных, если запись не соответствует критериям, поскольку триггеры рассматриваются как транзакции. Транзак­цией называется блок инструкций Transact-SQL, который интерпретиру­ется SQL Server как единое целое. Код группируется в транзакцию путем помещения инструкции BEGIN TRAN в начало кода и инструкции COMMIT в его конец. Эти ин­струкции могут размещаться как пользователем (явная транзакция), так и SQL Server (неявная транзакция). Поскольку триггер рассматривается как транзакция, вам нужно добавить лишь команду ROLLBACK в соответствующее место в коде. Команда ROLLBACK указывает серверу оста­новить обработку модификации и запретить транзакцию.

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

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

Существуют и другие способы реализации бизнес-логики. Используя связь внешнего ключа между таблицами клиентов и заказов, вы можете запретить удаление подвешенного заказа пользо­вателями. Вы также можете не позволить пользователю вставлять заказ для клиента отсутствующего в таблице клиентов. То же относится и к полномочиям удаления и обновления— действие нельзя будет выполнить в случае отмены полномочий. Если же подтвердить все полномочия, то пользователи смогут выполнять все действия.

Эти методы удобно использовать для внедрения бизнес-логики, такой как «в отделе маркетинга нельзя удалять данные, а только вставлять » или «записи клиентов с "висящими” заказами нельзя удалять ». Большинство компаний используют гораздо более сложную бизнес-логику. Они могут, к примеру, регламентировать правило бизнеса, гласящее следующее: «без разрешения менеджера нельзя устанавливать лимит кредитования клиентам больше 10 000 долларов или пользователь не может удалить данные о клиенте с кредитом больше 10 000 долларов ». Это довольно распространенные правила бизнеса, которые нельзя внедрить в таблице с помощью связей внешнего ключа или полномочий. Такую слож­ную деловую логику можно реализовать только с помощью триггеров. Мы начнем рас­смотрение с триггеров на вставку (INSERT).

Триггеры INSERT

Триггеры INSERT можно использовать для изменения или даже удаления вставлен­ной записи. Хорошим примером использования этого триггера является предотвращение добавления определенных типов записей, таких как данные клиентов с лимитом кредитования больше 10000 долларов. К качестве еще одного примера можно привести добавление или изменение запрещенных данных во вставляемой записи (к примеру, изменение даты создания записи или имени пользователя, вставляюще­го эту запись.

Триггеры INSERT запускаются (и выполняются) при каждой попытке создать новую запись в таблице с помощью команды INSERT. При попытке пользователя вста­вить новую запись в таблицу, SQL Server копирует эту запись в таблицу триггеров базы данных и в специальную таблицу, которая хранится в памяти и имеет имя inserted. Это означает, что ваша новая запись существует в двух таблицах — таблице триггеров и таблице inserted. Запись в таблице inserted должна полностью соответствовать записи в таблице триггеров.

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

UPDATE p

SET p.instock = {p.instock - i.qty)

FROM Products p JOIN inserted i ON p.prodid = i.prodid

Чтобы создать такой триггер и посмотреть, как он работает, вы должны выпол­нить несколько предварительных условий. Во-первых, вам нужна база данных Sales, созданная в работе 5. Во-вторых, вам нужно заполнить таблицу некоторыми значениями.

1. Откройте SQL Server Management Studio и выполните регистрацию с использованием аутентификации Windows или SQL Server.

2. Вставим в таблицу несколько записей о клиентах для продажи им товаров. Откройте новое окно запросов SQL Server и выполните следующий код заполнения таблицы клиентов информацией. (Для проверки значений за­пустите запрос SELECT * FROM customers).

USE Sales

INSERT customers

VALUES ('Gary','McKee', '111 Main', 'Palm Springs','CA','94312', ' 7605551212')

INSERT customers

VALUES ('Tom', 'Smith', '609 Georgia', 'Fresno', 'CA', '33045', '5105551212') INSERT customers

VALUES ('Shane', 'Travis', '806 Star', ‘Phoenix’, 'AZ1’, '85202', '6021112222')

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

INSERT Products

VALUES ('Giant Wheel of Brie', 200)

INSERT Products

VALUES ('Wool Blankers', 545)

INSERT Products

VALUES ('Espresso Beans', 1527)

INSERT Products

VALUES ('Notepads', 2098)

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

Итак, мы заполнили данными таблицы в базе данных Sales и готовы создать триг­гер, который будет автоматически обновлять столбец instock таблицы Products (товары на складе) на основе данных о товаре, проданном клиенту. Мы создадим триггер INSERT в таблице Orders (заказы на продажу), поскольку при продаже това­ра клиенту вы вставляете новую запись в таблицу Orders.

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

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

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

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

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

UPDATE p

SET p.instock = (p.instock - i.qty) FROM Products p JOIN inserted i ON p.prodid = i.prodid

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

Теперь вы можете протестировать триггер INSERT. В следующей последовательно­сти операций мы создадим в таблице Orders новую запись (имитируя таким образом заказ от клиента) для запуска триггера. Он должен уменьшить значение в столбце instock (остатки на складе) таблицы Products.

1. Откройте новый запрос SQL Server и выполните следующий код проверки значения instock для элемента 7 (оно должно быть равно 200) (рис. 2):

USE Sales

SELECT prodid, instock

FROM Products

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

USE Sales

INSERT Orders

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

Рис. 1. Создание нового триггера на вставку

Рис. 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, и вы сможете ссылаться на столбец с лимитом кредитования и обосновать успех транзакции на содержащемся в нем значении.