Alter table Заказ add Стоимость money Null

ALTER TABLE Заказ ADD СтоимостьНВ MONEY NULL

GO

Ç

Убедимся, что столбцы Стоимость и СтоимостьНВ (стоимость в национальной валюте) добавились в таблицу Заказ и во всех строках получили значение NULL:

É

SELECT * FROM Заказ

GO

Ç

Теперь создадим триггер для таблицы Товар, срабатывающий при любом изменении цены товара и корректирующий в таблице Заказ значения полей Стоимость и СтоимостьНВ в тех строках, которые соответствуют товару с изменившейся ценой:

É

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

ON Товар

FOR UPDATE AS

IF UPDATE(Цена)

BEGIN

-- объявляем локальные переменные

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

-- присваиваем значения локальным переменным, выбирая эти

-- значения из таблицы inserted

SELECT @КодТовара = inserted.КодТовара,

@Цена = inserted.Цена, @ЦенаНВ = inserted.Цена *

Валюта.КурсВалюты

FROM inserted INNER JOIN Валюта

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

-- обновляем значения стоимостей в таблице Заказ

UPDATE Заказ

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

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

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

END

GO

Ç

Проверим работу созданного триггера. Для этого изменим цену товара с кодом 111:

É

SELECT * FROM Товар

UPDATE Товар

SET Цена = 50

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

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

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

É

INSERT INTO Регион