Вопросы к допуску

1. Что называют хранимой процедурой?

2. За счет чего хранимые процедуры имеют большую производительность, нежели сценарии?

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

4. Перечислите виды хранимых процедур, используемых в MS SQL Server 2000. Дайте им краткую характеристику;

5. Как (в общем случае) хранятся процедуры?

6. Как осуществить вызов хранимой процедуры?

Контрольные вопросы

1. В какой базе данных хранятся системные хранимые процедуры? Приведите пример системной хранимой процедуры;

2. Где хранятся временные хранимые процедуры? Какой срок жизни может иметь временная хранимая процедура?

3. В каком виде хранятся расширенные хранимые процедуры? Как подключить собственную расширенную процедуры к серверу базы данных? Приведите пример расширенной хранимой процедуры;

4. Какими способами создаются локальные хранимые процедуры? Где и как они сохраняются?

5. Опишите синтаксис команды create proc;

6. Для чего используется опция with recompile?

7. Для чего используется опция with encryption?

8. Как создавать входные и выходные параметры в теле процедуры?

9. Опишите вызов процедуры с передачей параметров;

10. Опишите процесс передачи параметров из процедуры;

Лабораторная работа №7: «Создание триггеров»

Задание:

• Создать несколько триггеров разных типов и связать их с таблицами и представлениями базы данных;

• Продемонстрировать исполнение триггеров.

Теоретический материал:

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

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

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

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

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

• Если необходимо произвести обработку ошибок и организовать вывод пользовательских сообщений.

Во всех этих ситуациях применяются триггеры, т.к. функциональность ограничений не позволяет разрешать данные ситуации.

В MS SQL Server 2000, триггеры классифицируют по 2-м признакам: по поведению и по типу операции, на которую они реагируют. По поведению различают два вида триггеров – after и instead of. По типу операции триггеры делятся на insert trigger, update trigger и delete trigger

Триггеры типа after выполняются после того, как была выполнена запустившая их операция. Этот вид триггеров является стандартным триггером для MS SQL Server 2000. Данные триггеры можно связывать только с таблицами базы данных. Можно назначить несколько последовательно выполняемых триггеров на одну и ту же команду (insert, update или delete).

Триггеры типа instead of выполняются вместо запустивших их операций. Фактически, вместо запроса пользователя исполняется тело триггера. На каждую из операций можно назначить только один триггер данного типа, но зато их можно связывать не только с таблицами, но и с представлениями. На практике, триггеры этого типа применяют для обработки ошибок.

Для создания триггеров в T-SQL применяют команду create trigger, которая имеет следующий синтаксис:

CREATE TRIGGER имя_триггера

ON имя_таблицы

тип_триггера запускающая_операция

AS

тело триггера

GO

В команде обязательно требуется указать имя триггера и, после ключевого слова on имя таблицы (для instead of триггеров можно указать имя представления), с которой будет связан триггер. Далее требуется указать тип триггера – after (можно также использовать ключевое слово for) или instead of и операцию, которая запустит триггер. Один и тот же триггер может запускаться разными операциями, в этом случае их перечисляют через запятую.

После ключевого слова as следуют операторы T-SQL, составляющие тело триггера. Для завершения создания триггера выполняем команду go.

В процессе работы может понадобиться изменить триггер. Для этого используется команда alter trigger, которая имеет такой же синтаксис, как и команда create trigger. Фактически, в T-SQL, изменение триггера эквивалентно написанию его заново.

Если возникает необходимость удалить триггер, используется команда drop trigger имя_триггера.

Выясним, как работает триггер. При вызове триггера, MS SQL Server 2000 создает две специальные таблицы – inserted и deleted. Эти таблицы создаются отдельно для каждого триггера и имеют режим «только для чтения».

При вызове операции вставки данных (insert), таблица inserted содержит вставленные пользователем строки, а таблица deleted пуста. При вызове операции удаления (delete) ситуация противоположна – inserted пуста, а deleted содержит строки, удаленные пользователем из таблицы. При вызове операции обновления (update) задействуются обе таблицы. В таблице deleted содержатся данные, которые пользователь собирается изменить, а в таблице inserted содержатся обновленные данные.

При своей работе триггер анализирует данные, хранящиеся в таблицах inserted и deleted для принятия решения о фиксации или откате транзакции.

Достаточно часто на практике возникают ситуации, когда необходимо выяснить, в каком именно столбце (или столбцах) были произведены изменения. Язык T-SQL предоставляет функции update (имя_столбца) и column_updates (маска) для решения этой задачи.

Первая из этих функций - update (имя_столбца) позволяет в явном виде ввести имя некоторого столбца и возвращает true, если в нем были произведены вставка или обновление данных.

Для выполнения подобной проверки для нескольких столбцов, используется функция column_updated, принимающая в качестве параметра битовую маску. Для первых 8-ми столбцов маска формируется следующим образом (Таблица1):

Таблица 1

Столбец	1	2	3	4	5	6	7	8
Маска	1	2	4	8	16	32	64	128

На практике для реализации проверки применяют оператор битового and, данной внутри функции. Рассмотрим пример:

If (column_updates() & 40) = 0

В данном примере число 40 можно разложить на 32 + 8. Эти маски соответствуют шестому и четвертому столбцам. Таким образом, условный оператор с помощью функции column_updates проверяет, были ли внесены изменение и в 4, и 6 столбцы. Если бы потребовалось проверить были ли внесены изменения в хотя бы в один из этих столбцов, знак равенства заменился бы знаком «больше»:

If (column_updates() & 40) > 0

Следует заметить, что в таблицах часто содержится более восьми столбцов. Возникает вопрос: «Как проверить остальные столбцы?» Дело в том, что битовая маска одинакова для каждой восьмерки столбцов и для всех восьмерок, кроме первой необходимо указывать ее порядковый номер. Например, для второй восьмерки проверка 10 и 12 столбцов будет выглядеть следующим образом:

If (column_updates()&10, 2) > 0

В теле триггера можно использовать практически те же самые операторы, что и в хранимых процедурах, т.к. триггеры являются специальной разновидностью хранимых процедур. Однако, следует отметить, что в теле триггера нельзя создавать новые объекты – таблицы, представления и т.п.