Глава 8

Упражнение t Изучение хранимых процедур

Просмотр системных хранимых процедур в БД Master 6. Чем отличаются системные хранимые шедуры от расширенные едур. которые иачод*] кя в списке узла Stored Procedures?

Системные имые процедуры, как правило, имеют префикс а расширеннге - хр . Однако из этого правили есть исключения. Тим хранимое №Дуры можно проверить функцией OBJECTPROPERTY.

Упражнение 2. Работа с хранимыми процедурами

► Исполнение хранимой процедуры

2. лушсм примера i:. запуска хранимой процедур тло задана евое слово EXEC. Обязательно ли его использовать?

В данном случае ключевое слово EXEC или EXECUTE использовать не обязательно, поскольку запускаемая указана в строке оператора

Закрепление материала

1. Вы создали локальную временную хранимую процедуру и попросили коллегу исполнить ее. Однако у него ничего не вышло. Каковы возможные причины неудачи? Локальную временную хранимую процедуру может исполнить только ее создатель через соединение, использовавшееся для создания этой процедуры. После отключения соединения локальная временная хранимая процедура уничтожается.

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

быстрее, чем при первом запуске?

При первое мнснии хранимой процедуры создается ее план исполнения, и она компилируется. Повторная обработка скомпилированной хранимой процедуры выполняется Г)ы-стрее, поскольку SQL Server не проверяет заново не создает заново план ис-

полнения и не перекомпилирует процедуру. SQL Server хранит план исполнения в своем кэше процедур, который проверяется, прежде чем для процедуры создается новый план исполнения.

3. Какие средства защиты обеспечивают хранимые процедуры?

Пользователям БД можно дать разрешение на выполнение хранимой процедуры, но при этом не разрешить ем задаться к объектам БД, с которыми работает хранимая процедура нмую процедуру разрешается зашифровать при ее создании или изменении, так

что пользователи не смогут команды Transact SQL, составляющие процедуру.

4. Какая функция позволяет проверить свойства хранимой процедуры и других объектов SQL Server?

Различные атрибуты объектов БД (в том числе и хранимые процедуры) позволяет проверить функция OB.JECTPROPERTY. Вот пример использования функции OBJECTPROPERTY для проверки того, является ли хранимая процедура расширенной:

SELECT OBJECTPfiOPERTY(object ld{storedprocedurename), IsExtendedProc ]

3. Почему модификация хранимой процедуры с помощью ключевых слов ALTER PROCEDURE более эффективна, чем удаление процедуры с ее повторным созданием?

454 П(тлаже1М№

При модификации процедуры (без ее уничтожения и повторного создания) экономится время, поскольку большинство сволств процедуры (например, разрешения) при модификации не меняются и не удаляются.

6. База данные стапоилепа как текущая, и в ней создана хранимая процедура #Procedure01. Однако при проверке базы данных Northwind эта процедура не была найдена. Тем не мене ед\рУ удается запустить, есл шси базой данных является Northwind. Почему хранимая процедура запускается, по не обнаруживается в базе данных Northwind?

Добавление знака # к имен иной процедуры при ее создании приводит к созданию локальной временной хранимой процедуры. SQL Server создает ее в БД TempDB, следовательно, она не появляется в wiiwl. Эта процедура доступна из любой БД, без указания ее полного имени, при условии, что она исполняется через соединение, в котором она была создана.

7. Какие три функции выполняет оператор RETURN в хранимых процедурах? Во-первых, он обычно применяется Л1я возврата кода завершения, который можно проанализировать при обработке ошибок. Во-вторых, если в этом операторе не указано целочисленное значение, он проста шводит безусловные \од из хранимый процедуры. И, наконец, он может применяться для значений, не связанных с ошибочными ситуациями. Например, жнении KL1LRI. передавал целочисленное значение функции @@1DENTITY.

Глава 9 .. .

Закрепление материала

1. Вы определили для таблицы ограничения, один триггер INSTEAD OF и три триггера AFTER. Ваш коллега утверждает, что проконтролировать порядок срабатывания геров не удастся. Прав он или нет и почему?

Он не прав. Триггер INSTEAD всегда срабатывает перед обработкой ограничений, а после их обработки срабатывают триггеры AFTER. Поскольку триггеров AFTER три, первый и последний можно юределить посредством хранимой процедуры sp settrig{erorder.

2. При обновлении первичного ключа таблицы обязательным условием является обновление всех ссылок внешние 4eii на этот первичный ключ. Как решить эту задачу? Настроите дную ссылочную целостность для внешнего ключа, чтобы обновление первичного ключа сказывалось на таблицах.

3. Перечислите четыре случая, когда уместно использовать триггеры. Триггеры применяются юших случаях:

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

если необходимо каскадноение через связанные таблицы в базе данных;

если база данныщ1изовапа и требуется способ автоматизированного обновления избыточнгх данных в нескольких таблицах;

если необходимо сверить значение в одной таблице с неидентичным значением в другой

таблице;

если требуется вьшодпользова гельских сообщений и сложная обработка ошибок.

4. Каким образом триггер отслеживает изменения, которые он вносит в модифицируемую таблицу?

Триггеры INSERT и UPDATE создают в ламяти псевдотаблицу Inserted, которая содержит все добавленные или обновленные тайные. Триггер UPDATE также создает псевдотаблицу Deleted, содержапч-ю исходные дачные. Псевдотаблицу Deleted создает в памяти и трит-

гер DELETE. Эта таблица содержит все удаленные ньгс. Транзакция не фиксируется, пока не завершит работу триггер. Таким образом, триггер может откатить транзакцию.

5- Назовите событие удаления данные лицы, которое не вызывает срабатывания DELETE-тр)

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

6. Назовите системную хранимую процедуру ци ю. которые применяются для просмотра свойств триггера.

Свойства одного всех триггеров, применяемых для таблицы или представления, отображает системная хранимая процедура trigger. Для определения свойств объектов баз данных (таких, как триггеры) применяется функция Uii.jL*. liKOibiu i. ииирили-р, слелуюший код возвращает 1, если триггер к (К является триггером INSTEAD ОК

SELECT OBJECTPROPERTY (0BJECT IDCtrigg6r01), -ExecIsInsteadOfTrigger)

7. Какие два метода, позволяют предотвратить исполнение триггера?

Запретить запуск триггера можно посредством оператора ALTER TABLE. Например, чтобы запретить запуск триггера TriggerOl, установленного дл ицы TableOl, исполните оператор:

ALTER TABLE tabled DISABLE TRIGGER trlggerOI.

Вторая возможность - удаление триггера оператором DROP TRIGGER.

8. Запишите конструкцию (C0LUMNS LIPDATED(}. которая обнаруживает обновленные столбцы с порядковыми номерами 10 и II.

IF ((SUBSTRING(C0LUMNS UPDATED().2,1)=6)) PRINT Both columns 10 .о-.; 11 were updated,

9. Перечислите три задачи в базах данных, которые обычно решаются с помощью триггеров.

Получение промежуточнгх сумм и других вычисляемых значений; ауди яемых действий; выполнение внешний действий и реализация сложной целостности данных.

10. Какая команда позволяет запретить в триггере вывод сведений о подсчете строк для вызывающего приложения?

В триггере нужно указать: П1мт ON

1 i К какому типу относятся события, которые создают обе логические таблицы, Inserted

и Deleted?

Единственное событие, приводящее к созданию обеих псевдотаблиц - UPDATE. Таблица Inserted содержит новые значения, заданные при обновлении, а таблица Deleted содержит изначальные значения, существовавшие до исполнения UPDATE. 12. Можно ли запрограммировать в триггерах вывод результирующих наборов и сообщений? Да, результирующие наборы отображаются посредством оператора SELECT, а сообшення на экран выводятся командой PRINT. Однако применять SELECT и PRINT для вывода результатом можно лишь в том случае, если известно, что все приложения, модифицирующие таблицы, способны обрабатывать возвращаемые данные.