Материал: BD_Laboratornyi_774_praktikum

SELECT * FROM Товар

GO



Теперь посмотрим содержимое таблицы Заказ и убедимся, что подсчитались стоимости заказов, относящиеся к товарам с кодом 111:



SELECT * FROM Заказ

GO



Однако данный триггер обладает тем недостатком, что отслеживает изменение цены лишь одного товара, даже если были одновременно изменены цены сразу у нескольких товаров. Убедимся в этом, изменив цены сразу для всех товаров:



SELECT * FROM Товар

UPDATE Товар

SET Цена = Цена * 2

SELECT * FROM Товар

GO



Снова посмотрим содержимое таблицы Заказ и убедимся, что подсчитались стоимости заказов только по товару, представленному первой строкой таблицы Товар:



SELECT * FROM Заказ

SELECT * FROM Товар

GO



Устраним указанный выше недостаток путем внесения изменения в код триггера:



ALTER TRIGGER tr_Товар_Цена

ON Товар

FOR UPDATE AS

If update(Цена)

BEGIN

DECLARE @КодТовара INT, @Цена MONEY, @ЦенаНВ MONEY

-- объявляем курсор myCursor. Набор данных, связанный с курсором и

-- построенный на основе таблицы inserted, будет содержать три столбца

DECLARE myCursor CURSOR LOCAL STATIC

FOR

SELECT inserted.КодТовара, inserted.Цена,

Inserted.Цена * Валюта.КурсВалюты

FROM inserted INNER JOIN Валюта

ON inserted.КодВалюты = Валюта.КодВалюты

-- открываем курсор

OPEN myCursor

-- заносим в курсор данные первой строки набора данных и считываем

-- значения ее полей в переменные @КодТовара, @Цена, @ЦенаНВ

FETCH FIRST FROM myCursor INTO @КодТовара, @Цена, @ЦенаНВ

-- организуем цикл, необходимый для последовательной работы с остальными

-- строками набора данных с целью обновления стоимостей в таблице Заказ

WHILE @@FETCH_STATUS = 0

BEGIN

UPDATE Заказ

SET Стоимость = Количество * @Цена,

СтоимостьНВ = Количество * @ЦенаНВ

WHERE КодТовара = @КодТовара

FETCH NEXT FROM myCursor INTO @КодТовара, @Цена,

@ЦенаНВ

END

-- закрываем курсор

CLOSE myCursor

-- освобождаем курсор

DEALLOCATE myCursor

END

GO



Проверим работу триггера, снова изменив цены сразу для всех товаров, после чего убедимся, что пересчитались стоимости всех заказов, представленных строками таблицы Заказ:



SELECT * FROM Товар

UPDATE Товар

SET Цена = Цена / 2

SELECT * FROM Товар

SELECT * FROM Заказ

GO



Задание к работе

1. Создайте для таблицы Заказ триггер tr_Set_СрокПоставки, с помощью которого в этой таблице будет автоматически устанавливаться значение поля СрокПоставки при вставке новой записи и при обновлении поля ДатаЗаказа следующим образом:

- если цена товара определена в белорусских рублях, то срок поставки равен дате заказа плюс 3 дня;

- если цена товара определена в российских рублях или украинских гривнах, то срок поставки равен дате заказа плюс 7 дней;

- если цена товара определена в долларах США или евро, то срок поставки равен дате заказа плюс 10 дней;

- если цена товара определена в валюте, отличной от указанных выше валют, то срок поставки равен дате заказа плюс 14 дней.

2. Добавьте в базу данных новую таблицу Отпуск, которая снабжена связью типа «один к одному» с таблицей Товар:



CREATE TABLE Отпуск (

КодТовара INT PRIMARY KEY,

Наименование VARCHAR(50) NOT NULL,

ВсегоЗаказано NUMERIC(12, 3) NULL,

CONSTRAINT FK_Отпуск_Товар FOREIGN KEY (КодТовара)

REFERENCES Товар ON UPDATE CASCADE

)



Создайте для таблицы Заказ триггер tr_Кол _ЗаказанногоТовара, с помощью которого в таблице Отпуск будет автоматически обновляться информация о суммарном количестве заказанного товара. Триггер должен срабатывать при операциях вставки и удаления строк в таблице Заказ, а также при обновлении в ней поля Количество. Если в таблице Отпуск еще нет строки, подлежащей корректировке (а так вначале и будет), то должна быть выполнена операция вставки новой строки, в которой в поле ВсегоЗаказано записывается суммарное количество заказанного товара, подсчитанное на основе всех строк таблицы Заказ, связанных с данным товаром.

3. Создайте хранимую процедуру pr_Стоимость_ВалютаИнтервал для решения более общей задачи по сравнению с задачей, рассмотренной в разделе I, а именно: необходимо подсчитать суммарную стоимость всех товаров, заказанных в течение указанного интервала времени, однако не в национальной валюте, а в валюте, указанной пользователем (в частности, может быть указана и национальная валюта). Эта процедура должна иметь три входных параметра (@КодВалюты, @НачалоИнтервала, @КонецИнтервала) и один выходной параметр (@Стоимость).

4. Добавьте в таблицу Регион две новые строки, используя следующие команды:



INSERT INTO Регион

VALUES (102, 'Россия', '', 'Москва', 'пр.Калинина, 50',

'339-62- 10', '(095) 339-62-11')

INSERT INTO Регион

VALUES (401, 'Литва', '', 'Вильнюс', 'ул.Чурлёниса, 19', NULL,

'(055) 33-27-75')

GO



Разработайте программный код (не обязательно в виде хранимой процедуры), который формирует таблицу следующего вида:

При этом используйте созданную при выполнении предыдущей лабораторной работы хранимую процедуру pr_КлиентПоставщик_СтранаИнтервал, которая подсчитывает, сколько различных клиентов и поставщиков из указанной страны фигурирует в таблице Заказ (за указанный интервал времени). Эта хранимая процедура должна применяться для формирования каждой строки в указанной выше таблице. Число строк этой таблицы должно равняться числу различных стран, фигурирующих в таблице Регион.

5. Создайте таблицу Протокол со структурой, приведенной ниже, в которой должны автоматически фиксироваться все действия, вызванные вставкой, обновлением или удалением данных в таблице Товар. Каждая команда, изменяющая содержимое таблицы Товар, должна быть отражена отдельной строкой в таблице Протокол.

Здесь столбец Номер является автоинкрементным первичным ключом. В столбце Действие указывается одна из трех возможных операций с данными: «Вставка», «Обновление», «Удаление». Столбец ЧислоСтрок будет содержать данные о числе вставленных, либо обновленных, либо удаленных строк в таблице Товар.

Усложненный вариант. Таблица Протокол должна включать в себя еще один столбец КодыТоваров, в котором указываются коды товаров, фигурирующие во вставленных, обновленных или удаленных строках.

6. Доведите до завершения рассмотренную выше в виде примера задачу корректировки значений полей Стоимость и СтоимостьНВ в таблице Заказ. Значения этих полей должны автоматически обновляться не только при изменении цены товара (как было реализовано в примере), но и при изменении количества заказанного товара, а также вставке новых строк в таблицу Заказ. Кроме того, значение столбца СтоимостьНВ должно автоматически обновляться также при изменении курса соответствующей валюты.

7. Разработайте программный код для проверки работы разработанных хранимых процедур и триггеров.

8. Фрагменты кода, относящиеся к выполнению задания, сохраните в файле D:\Work\X7230ХХХ\script4.sql.

7. Удалите базу данных Склад_ХХХ. Для этого в ее контекстном меню выберите команду Delete и затем в появившемся окне Delete Object установите флажок Close Existing Connections.

Литература Основная

1. Мамаев, Е.Н. Microsoft SQL Server 2000 / Е.Н. Мамаев. – СПб.: БХВ-Петербург, 2001.

2. Шкарина, Л.Н. Язык SQL. Учебный курс / Л.Н. Шкарина. – СПб.: Питер, 2003.

3. Маклаков, С.В. BPwin и ERwin. CASE-средства разработки информационных систем / С.В. Маклаков. – М.: ДИАЛОГ-МИФИ, 1999.

4. Сеннов, А. С. Access 2010. Учебный курс / А.С. Сеннов. – СПб.: Питер, 2010.

5. Роланд, Ф.Д. Основные концепции баз данных / Ф.Д. Роланд. – М.: Издательский дом «Вильямс», 2002.

6. Сичкаренко, В.А. SQL-99. Руководство разработчика баз данных / В.А. Сидоренко. – СПб: ООО «ДиаСофтЮП», 2002.

7. Андон, Ф.И. Язык запросов SQL. Учебный курс / Ф.И. Андон, В.А. Резниченко. – СПб.: Питер, 2006.

8. Вишневский, А.В. Microsoft SQL Server. Эффективная работа / А.В. Вишневский. – СПб.: Питер, 2008.

9. Карпова, И.П. Базы данных. Учебное пособие / И.П. Карпова. – СПб.: Питер, 2016.

10. Бондарь, А.Г. Microsoft SQL Server 2014 / А.Г. Бондарь. – СПб.: БХВ-Петербург, 2015.

11. Жилинский, А.А. Самоучитель Miсrosoft SQL Server 2008 / Жилинский А.А. – СПб.: БХВ-Петербург, 2009.

12. Мирошниченко Г.А. Реляционные базы данных. Практические приемы оптимальных решений / Г.А. Мирошниченко. – СПб.: БХВ-Петербург,  2005.

Дополнительная

13. Коннолли, Т. Базы данных / Т. Коннолли, Б. Каролин. – М.: Издательский дом «Вильямс», 2003.

14. Дейт, К. Введение в системы баз данных. 8-е издание / Пер. с англ. / К. Дейт. – М.: Издательский дом «Вильямс», 2006.

15. Грофф, Дж. Энциклопедия SQL / Дж. Грофф, П. Вайнберг. – СПб.: Питер, 2003.

16. Кренке, К. Теория и практика построения баз данных / К. Кренке. – СПб.: Питер, 2005.

17. Роб, П. Системы баз данных: проектирование, реализация и управление / П. Роб, К. Корнел. – СПб.: БХВ-Петербург, 2004.

18. Хансен, Г. Базы данных: разработка и использование / Пер. с англ. / Г. Хансен, Дж. Хансен. – М.: БИНОМ, 2000.

19. Сеннов, А.С. Access 2003. Практическая разработка баз данных. Учебный курс / А.С. Сеннов. – СПб.: Питер, 2006.

20. Уилтон, П. Язык запросов SQL для начинающих / П. Уилтон, Дж. Колби. – М.: Диалектика, 2006.

21. Уолтерс, Р. SQL Server 2008: ускоренный курс для профессионалов / Р. Уолтерс, М. Коулс, Р. Рей и др. – М.: Диалектика, 2008.

22. Виейра, Р. Программирование баз данных Microsoft SQL Server 2008. Базовый курс / Р. Виейра. – М.: Диалектика, 2009.

23. Петкович, Д. Microsoft SQL Server 2012. Руководство для начинающих / Д. Петкович. – СПб.: БХВ-Петербург, 2013.

24. Хомоненко, А.Д. Microsoft Access. Экспресс-курс / А.Д. Хомоненко, В.В.Гридин. – СПб.: БХВ-Петербург, 2005.

25. Когаловский, М.Р. Энциклопедия технологий баз данных / М.Р.Когаловский. – М.: Финансы и статистика, 2002.

26. Малыхина, М.П. Базы данных: Учеб. Пособие / М.П. Малыхина. – СПб.: БХВ-Петербург, 2004.

27. Петров, В.Н. Информационные системы / В.Н. Петров. – СПб.: Питер, 2002.

28. Пирогов, В.Ю. Информационные системы и базы данных: организация и проектирование / В.Ю. Пирогов. – СПб.: БХВ-Петербург, 2009.

29. Хомоненко, А.Д. Базы данных: Учеб. для вузов / А.Д. Хомоненко, В.М. Цыганков, М.Г. Мальцев; Под ред. А.Д. Хомоненко. – СПб.: КОРОНА принт, 2004.

30. Карпова, Т.С. Базы данных: модели, разработка, реализация: Учеб. для вузов / Т.С. Карпова. – СПб.: Питер, 2001.

31. Рудикова, Л.В. Базы данных. Разработка приложений / Л.В. Рудикова. – СПб.: БХВ-Петербург, 2006.

32. Грабор, М. Введение в SQL / М. Грабор. – М.: Лори, 1996.