Lab9-10_SQL (1059137)
Текст из файла
Задание 9-10
Использование триггеров
-
Подключитесь к БД Sales.
-
Cоздайте и протестируйте триггер INSERT для таблицы Orders
-
Cоздайте и протестируйте триггер DELETE для таблицы Orders.
-
Cоздайте и протестируйте триггер UPDATE для таблицы Products.
-
Cоздайте и протестируйте триггер UPDATE для запрещения обновления какого-либо столбца таблицы.
-
Используйте триггер INSTEAD OF для обновления таблицы.
-
Создайте и протестируйте триггер DDL, который предотвратит удаление или изменение таблицы в базе данных Sales.
-
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. Во-вторых, вам нужно заполнить таблицу некоторыми значениями.
-
Откройте SQL Server Management Studio и выполните регистрацию с использованием аутентификации Windows или SQL Server.
-
Вставим в таблицу несколько записей о клиентах для продажи им товаров. Откройте новое окно запросов 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.
-
Откройте SQL Server Management Studio и раскройте папки сервера Databases=> Sales=>Tables=> dbo.Orders.
-
Щелкните правой кнопкой мыши на папке Triggers и выберите команду New Trigger.
-
В строке CREATE TRIGGER введите имя триггера (InvUpdate), в строке ON введите имя таблицы (dbo.Orders).
-
В строке AFTER оставьте INSERT (удалите Update и Delete).
-
В поле (--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, и вы сможете ссылаться на столбец с лимитом кредитования и обосновать успех транзакции на содержащемся в нем значении.
Характеристики
Тип файла документ
Документы такого типа открываются такими программами, как Microsoft Office Word на компьютерах Windows, Apple Pages на компьютерах Mac, Open Office - бесплатная альтернатива на различных платформах, в том числе Linux. Наиболее простым и современным решением будут Google документы, так как открываются онлайн без скачивания прямо в браузере на любой платформе. Существуют российские качественные аналоги, например от Яндекса.
Будьте внимательны на мобильных устройствах, так как там используются упрощённый функционал даже в официальном приложении от Microsoft, поэтому для просмотра скачивайте PDF-версию. А если нужно редактировать файл, то используйте оригинальный файл.
Файлы такого типа обычно разбиты на страницы, а текст может быть форматированным (жирный, курсив, выбор шрифта, таблицы и т.п.), а также в него можно добавлять изображения. Формат идеально подходит для рефератов, докладов и РПЗ курсовых проектов, которые необходимо распечатать. Кстати перед печатью также сохраняйте файл в PDF, так как принтер может начудить со шрифтами.
![](https://s.studizba.com/z.php?f=/templates/si/i/noavatar.png&w=100&h=100&t=1"){kind=avatar}
