27.3.2. Хранимые процедуры (слайд 11)

Хранимая процедура (stored procedure) – это набор операторов T-SQL, которые SQL SERVER компилирует в единый план выполнения. Этот план сохраняется в кэше процедур при первом выполнении хранимой процедуры, и затем план можно повторно использовать уже без рекомпиляции при каждом вызове. Хранимая процедура аналогична процедурам в языках программирования: она может принимать входные параметры, возвращать данные и коды завершения.

Применение хранимых процедур улучшает производительность, например, при использовании в хранимой процедуре условных операторов (таких как IF и WHILE), поскольку условие будет проверяться непосредственно на сервере и серверу не потребуется возвращать промежуточные результаты проверки условия программам-клиентам.

Хранимые процедуры также позволяют централизованно контролировать выполнение задачи, что гарантирует соблюдение бизнес-правил.

Хранимые процедуры, как и представления, можно создавать различными способами: использовать для этого «мастер» или команду T-SQL, имеющую в общем случае следующий формат

CREATE PROCEDURE имя_процедуры [(%параметр1 тип_данных[,..]]

AS SQL-операторы

Существует два типа хранимых процедур: системные и пользовательские. Первые поддерживается SQL Server и применяются для управления сервером и отображения информации о базах данных и пользователях. Вторые создаются пользователями для выполнения прикладных задач.

27.3.3. Триггеры (слайд 11)

Триггер (trigger) - это особый тип хранимой процедуры, которая автоматически выполняется при изменении таблицы с помощью операторов UPDATE, INSERT или DELETE. Как и хранимые процедуры, триггеры содержат операторы T-SQL, но в отличие от процедур запускаются не индивидуально, а автоматически при выполнении операций изменения данных. Триггеры наряду с ограничениями обеспечивают целостность данных и соблюдение бизнес-правил, однако их следует использовать разумно. Например, не нужно создавать триггер, проверяющий наличие значения первичного ключа в одной таблице, чтобы определить можно ли вставить значение в соответствующее поле другой таблицы. Однако трудно обойтись без триггеров при выполнении каскадных изменений в дочерних таблицах.

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

Существуют три типа триггеров: UPDATE, INSERT и DELETE, каждый из которых инициируется при выполнении одноименной команды. Операции UPDATE, INSERT и DELETE иногда называют событиями изменения данных. Можно создать триггер, который будет срабатывать при возникновении более чем одного события, например, запускаться в ответ на операторы UPDATE или INSERT. Такие комбинированные триггеры называются UPDATE/INSERT. Возможно создание триггеров, срабатывающих при выполнении любого из трех операторов обновления данных (триггер UPDATE/INSERT/DELETE).

Триггеры, как и представления, можно создавать различными способами: использовать для этого «мастер» или команду T-SQL, имеющую следующую в общем случае формат:

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

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

FOR [INSERT] [,] [UPDATE] [,] [DELETE]

AS SQL-операторы

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

Работа триггеров подчиняется следующим правилам:

• Триггеры запускаются только после завершения выполнения вызывающего их оператора. Например, триггер UPDATE не начинает работать, пока не завершится выполнение оператора UPDATE.

• Триггер не начинает работать, если при выполнении оператора происходит нарушение какого-либо ограничения таблицы или возникает другая ошибка.

• Триггер и вызывающий его оператор образуют транзакцию. В результате вызова из триггера оператора ROLLBACK отменяются изменения, выполненные триггером и оператором. При возникновении серьезной ошибки, например, при отключении пользователя, SQL-Server автоматически выполнит откат всей транзакции.

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

Триггеры возвращают результаты своей работы в приложение, подобно хранимым процедурам. Как правило, пользователь не ожидает вывода после выполнения операторов UPDATE, INSERT и DELETE, вызывающих срабатывание триггеров. Если триггер возвращает данные, приложение должно содержать код, правильно интерпретирующий результаты модификации таблицы и вывод триггера.

Для каждого триггера SQL Server создает две временные таблицы, на которые можно ссылаться в описании триггера. Эти таблицы хранятся в памяти и локальны по отношению к триггеру, то есть триггер имеет доступ только к своей собственной версии таблиц. Временные таблицы применяются для сравнения состояния таблицы до и после внесения изменений.

В MS SQL Server возможно создание нескольких триггеров на таблице для каждого события изменения данных (UPDATE, INSERT или DELETE) и рекурсивный вызов триггера. Например, если для таблицы уже определен триггер UPDATE, то можно определить еще один триггер UPDATE для той же самой таблицы. В этом случае после выполнения соответствующего оператора сработают оба триггера. Кроме того, допускаются вложенные триггеры, которые срабатывают в результате выполнения других триггеров. Они отличаются от рекурсивных тем, что не запускают сами себя.