Материал: BD_Laboratornyi_774_praktikum

If @НачалоИнтервала is null

SET @НачалоИнтервала = getdate() - 365

If @КонецИнтервала is null

SET @КонецИнтервала = getdate()

-- выполняем начальную установку

SET @СтоимостьНВ = 0

-- объявляем локальную переменную @СтоимостьЗаказа

DECLARE @СтоимостьЗаказа MONEY

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

-- с курсором, будет содержать всего один столбец

DECLARE myCursor CURSOR LOCAL STATIC

FOR

SELECT Заказ.Количество * Товар.Цена * Валюта.КурсВалюты

FROM Заказ

INNER JOIN Товар ON Заказ.КодТовара = Товар.КодТовара

INNER JOIN Валюта ON Товар.КодВалюты = Валюта.КодВалюты

WHERE Заказ.ДатаЗаказа BETWEEN @НачалоИнтервала AND

@КонецИнтервала

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

OPEN myCursor

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

-- считываем это значение в переменную @СтоимостьЗаказа

FETCH FIRST FROM myCursor INTO @СтоимостьЗаказа

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

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

WHILE @@FETCH_STATUS = 0

BEGIN

SET @СтоимостьНВ = @СтоимостьНВ + @СтоимостьЗаказа

FETCH NEXT FROM myCursor INTO @СтоимостьЗаказа

END

-- Опцию NEXT (переход к следующей строке) в команде FETCH можно

-- опускать, т.к. она подразумевается по умолчанию

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

CLOSE myCursor

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

DEALLOCATE myCursor

GO

/* Проверка работы хранимой процедуры pr_СтоимостьНВ_Интервал */

DECLARE @Cost MONEY

EXEC pr_СтоимостьНВ_Интервал NULL, NULL, @Cost OUTPUT

SELECT getdate()-60 AS [Начало интервала], getdate() AS [Конец

интервала], @Cost AS [Стоимость заказов в НВ]

DECLARE @BeginDate DATETIME, @EndDate DATETIME

SET DATEFORMAT dmy

SET @BeginDate = '10.04.2013'

SET @EndDate = '30.12.2013'

EXEC pr_СтоимостьНВ_Интервал @BeginDate, @EndDate, @Cost OUTPUT

SELECT @BeginDate AS [Начало интервала], @EndDate AS [Конец

интервала], @Cost AS [Стоимость заказов в НВ]

GO



Раздел II. Создание триггеров в языке Transact-sql.

Триггеры представляют собой хранящиеся в базах данных подпрограммы на языке SQL, выполняющиеся автоматически при операциях вставки, обновления и удаления данных в таблицах базы данных. Каждый триггер связан только с одной из таблиц базы данных. Автоматическое срабатывание триггеров в ответ на изменения табличных данных позволяет использовать их, например, для реализации сложных алгоритмов проверки данных, для гарантии их правильности и достоверности, для создания сложного значения по умолчанию, вычисляя его с помощью других столбцов и функций Transact-SQL, для обеспечения нестандартной ссылочной целостности, поддержание которой обычными средствами SQL Server невозможно и т.д. Использование триггеров превращает сервер из пассивного наблюдателя за происходящими изменениями данных, в систему, оперативно реагирующую на такие изменения. Таким образом, правила, в соответствие с которыми осуществляются активные действия сервера, определяются триггерами (эти правила называют также бизнес-правилами).

4. Закрытие курсора. После того, как были выполнены все необходимые манипуляции со строками курсора, его можно закрыть. Это приводит к высвобождению выделенных для него ресурсов (например, пространства в системной базе данных tempdb) и снятию блокировок, если они были установлены в процессе работы курсора. Команда имеет следующий синтаксис (см.[1], стр. 1228):

CLOSE { { [ GLOBAL ] cursor_name } | cursor_variable_name }

5. Освобождение курсора. Закрытый курсор может быть удален, что подразумевает удаление из оперативной памяти описания курсора как объекта. Если курсор закрыт, но не удален, он может быть повторно открыт для использования. При этом в него будет помещен новый набор данных.

Команда освобождения курсора имеет следующий синтаксис (см. [1], стр. 1237):

DEALLOCATE { { [ GLOBAL ] cursor_name } | @cursor_variable_name }

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

Одним из вариантов решения этой задачи будет создание хранимой процедуры pr_СтоимостьНВ_Интервал с двумя входными параметрами (@НачалоИнтервала, @КонецИнтервала) и одним выходным параметром (@СтоимостьНВ).



/* Подсчет стоимости товаров в национальной валюте */

CREATE PROCEDURE pr_СтоимостьНВ_Интервал

@НачалоИнтервала DATETIME,

@КонецИнтервала DATETIME,

@СтоимостьНВ MONEY OUTPUT

AS

В SQL Server 2000/2005 существует два вида триггеров:

• AFTER-триггеры, которые запускаются после успешного выполнения команд, связанных с изменением табличных данных. Как команда, так и триггер реализуются в рамках одной и той же транзакции. Поэтому откат при выполнении триггера приведет и к откату команды, вызвавшей его запуск. AFTER-триггеры широко используются и полезны, например, в тех случаях, когда при модификации строк необходимо сравнивать исходные значения полей с их новыми значениями. С каждой таблицей может быть связано несколько AFTER-триггеров;

• INSTEAD OF-триггеры, тело которых выполняется вместо операций вставки, обновления и удаления строк, вызвавших запуск триггера этого вида. С каждой таблицей может быть связано не более трех AFTER-триггеров (по одному для каждой из команд INSERT, UPDATE, DELETE). Триггеры этого вида могут создаваться не только для таблиц, но и для представлений.

Синтаксис команды создания триггера (см. [1], стр. 1242):

CREATE TRIGGER trigger_name ON { table | view } [ WITH ENCRYPTION ] {     { FOR [ { AFTER | INSTEAD OF }]

{ [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] }         [ WITH APPEND ]         [ NOT FOR REPLICATION ]         AS         [ { IF UPDATE ( column )             [ { AND | OR } UPDATE ( column ) ]                 [ ...n ]         | IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask )                 { comparison_operator } column_bitmask [ ...n ]         } ]         sql_statement [ ...n ]     } }

В этой команде, в частности, присутствуют функции UPDATE(column) и COLUMNS_UPDATED( ), используемые для определения того, какой столбец или группу столбцов пользователь пытается изменить (см. [1], стр. 1244-1246). Кроме того, всегда можно получить полную информацию об изменениях, которые пытается произвести пользователь. Эту информацию дают таблицы inserted и deleted, которые автоматически создаются сервером при запуске триггера. Содержимое этих таблиц зависит от команды, вызвавшей запуск триггера:

• Команда INSERT. В таблице inserted будут содержаться все строки, которые пользователь пытается вставить в таблицу. Таблица deleted будет пуста.

• Команда DELETE. В таблице deleted будут содержаться все строки, которые пользователь пытается удалить. Таблица inserted будет пуста.

• Команда UPDATE. В таблице deleted будут содержаться все строки, которые пользователь пытается изменить. В таблице inserted указываются строки, которые будут внесены в таблицу вместо соответствующих строк таблицы deleted.

Для внесения изменений в текст существующего триггера используется та же команда, что и для его создания, с тем лишь отличием, что вместо зарезервированного слова CREATE используется слово ALTER (см. [1], стр. 1246).

Для удаления триггера используется команда, имеющая следующий синтаксис (см. [1], стр. 1247):

DROP TRIGGER { trigger } [ ,...n ]

В кодах триггеров часто используется команда ROLLBACK TRAN (отмена или откат транзакции). Кроме нее в программах на языке Transact-SQL используются также команды BEGIN TRAN (старт транзакции), COMMIT TRAN (подтверждение транзакции) и SAVE TRAN (создание точки сохранения транзакции). Подробнее см. в [1], стр. 1247. Например, отладку какой-нибудь команды или фрагмента программы, вносящих изменения в данные, можно начать со старта транзакции, а в самом конце выполнить команду ROLLBACK TRAN, восстановив тем самым первоначальные значения измененных данных:



SELECT * FROM Валюта -- просмотр исходных данных

BEGIN TRAN -- старт транзакции

UPDATE Валюта -- обновление данных

SET КурсВалюты = КурсВалюты * 2

SELECT * FROM Валюта -- просмотр измененных данных

ROLLBACK TRAN -- откат транзакции

SELECT * FROM Валюта -- снова просмотр исходных данных

GO



Рассмотрим два примера по созданию триггеров.

Пример 1. Запретим с помощью триггера возможность модификации данных в столбце ДатаЗаказа таблицы Заказ.



CREATE TRIGGER tr_Заказ_ДатаЗаказа

ON Заказ

FOR UPDATE AS

If update(ДатаЗаказа)

BEGIN

PRINT 'Обновление столбца "ДатаЗаказа" запрещено'

ROLLBACK TRAN -- откат транзакции

END

GO



Примечание. Команда PRINT в отличие от команды SELECT выводит сообщения не на панель Results, а на панель Messages окна Query утилиты SQL Server Management Studio.

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



SELECT * FROM Заказ -- команда 1

UPDATE Заказ -- команда 2

SET ДатаЗаказа = ДатаЗаказа + 10

GO -- команда 3

SELECT * FROM Заказ -- команда 4



Примечание. Все 4 команды, приведенные выше, выделите подсветкой в окне Query и выполните за один раз. Тогда будут показаны две таблицы Заказ (до и после корректировки).

Пример 2. Предварительно добавим в таблицу Заказ два новых столбца: Стоимость и СтоимостьНВ:



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