Хранимые процедуры триггеры и представления
Хранимая процедура — последовательность команд SQL, хранящаяся на сервере БД в скомпилированном виде. Хранимые процедуры составляют для часто выполняемых операций. Например, каждый месяц сотрудникам надо пересчитывать зарплату. Можно создать хранимую процедуру, производящую перерасчет, и таким образом сэкономить время. Хранимая процедура может принимать параметры и возвращать результаты работы. Когда приложение использует процедуру, оно передает параметры, если они есть, а сервер СУБД затем выполняет хранимую процедуру без повторной компиляции (рис. 1.13).
Рис. 1.13. Выполнение хранимой процедуры
Использование хранимых процедур имеет несколько преимуществ с точки зрения производительности:
1.
Так как хранимые процедуры уже скомпилированы и записаны, то серверу требуется меньше времени для их выполнения.
2.
Снижается нагрузка на сеть, так как передается меньшее число операторов. Создать хранимую процедуру довольно просто. Следующий фрагмент кода иллюстрирует этот процесс:
CREATE PROC give_raise AS
UPDATE EMPLOYEES SET salary = salary * 1.10
В данном примере создается хранимая процедура give_raise, модифицирующая таблицу EMPLOYEES. Значение поля salary увеличивается на 10%.
Понятие триггера
Триггер — хранимая процедура, вызов которой происходит автоматически при выполнении с базой данных определенных действий: удаление, изменение, добавление записей. В зависимости от того, какой оператор модификации данных активизирует триггер, он называется триггером вставки (insert trigger), триггером удаления (delete trigger) или триггером обновления (update trigger).
Триггеры часто используются для обеспечения целостности на уровне ссылок, для выполнения каскадных удалений. Так как триггеры вызываются самой СУБД, нет возможности напрямую вызвать их из клиентского приложения. Триггер может быть вызван неявно другим триггером при совершении некоторых действий с базой данных:
USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = ‘reminder’ AND type = ‘TR’)
DROP TRIGGER reminder
GO
CREATE TRIGGER reminder
ON titles
FOR INSERT, UPDATE, DELETE
AS
EXEC master.,xp_sendmail 'MaryM',
'DorT't forget to print a report for the distributors.'
GO
В данном примере приведено определение триггера reminder. Он выполняется при удалении, добавлении или изменении записи. В теле триггера вызывается хранимая процедура xpsendmail, отправляющая некой Мэри М. по электронной почте соответствующее напоминание.
Представление
Представление является виртуальной таблицей, записи в которую собраны с помощью оператора Select из одной и более таблиц. Работа с представлением осуществляется, как с обычной таблицей:
CREATE VIEW Example AS
SELECT Fieldl, Field2
FROM EXTABLE
WHERE Field3 = 3
SELECT * FROM Example
В приведенном примере создается представление Example, в которое включаются поля Fieldl и Field2. Выбираются записи, в которых значение поля Field2 равно трем. Последний оператор вызывает просмотр. Само по себе представление
не содержит данных, оно их получает каждый раз при вызове. Представления подобны окнам, через которые разработчик или пользователь просматривает информацию, которая фактически хранится в базовой таблице.
Преимущество использования представления заключается в том, что представление
будет модифицировано автоматически всякий раз, когда таблица, лежащая в его основе, изменяется. Содержание представления не фиксировано и переназначается каждый раз, когда оно вызывается тем или иным оператором SQL. В большинстве случаев представления могут использоваться как механизм защиты данных, поскольку даются права доступа к представлению базы данных, а не к самим таблицам, содержащим данные.
Представления могут быть модифицируемыми или позволяющими только чтение данных. Модифицируемое представление позволяет изменять данные в таблице.
Для того чтобы представление было модифицируемым, оно должно удовлетворять списку условий:
- В представлении должны быть собраны записи только из одной таблицы.
- В операторе SELECT не должны использоваться статистические функции, описатель DISTINCT, операнд HAVING, а также соединения таблиц.
- Представление не должно содержать полей, которые являлись бы агрегатными функциями.
- Для команды INSERT представление должно содержать любые поля таблицы, имеющие ограничение NOT NULL, если не задано иное ограничение.
В качестве примера можно взять модифицированное представление Example и показать, как в него добавляются записи при помощи небольшого фрагмента кода:
INSERT INTO Example
VALUES (333, ‘fds”)
Типы триггеров
В SQL Server существует два параметра, определяющих поведение триггеров:
· AFTER. Триггер выполняется после успешного выполнения вызвавших его команд. Если же команды по какой-либо причине не могут быть успешно завершены, триггер не выполняется. Следует отметить, что изменения данных в результате выполнения запроса пользователя и выполнение триггера осуществляется в теле одной транзакции: если произойдет откат триггера, то будут отклонены и пользовательские изменения. Можно определить несколько AFTER-триггеров для каждой операции (INSERT, UPDATE, DELETE). Если для таблицы предусмотрено выполнение нескольких AFTER-триггеров, то с помощью системной хранимой процедурыsp_settriggerorder можно указать, какой из них будет выполняться первым, а какой последним. По умолчанию в SQL Server всетриггеры являются AFTER-триггерами.
· INSTEAD OF. Триггер вызывается вместо выполнения команд. В отличие от AFTER-триггера INSTEAD OF-триггер может быть определен как для таблицы, так и для просмотра. Для каждой операции INSERT, UPDATE, DELETE можно определить только одинINSTEAD OF-триггер.
Триггеры различают по типу команд, на которые они реагируют.
Существует три типа триггеров:
· INSERT TRIGGER – запускаются при попытке вставки данных с помощью команды INSERT.
· UPDATE TRIGGER – запускаются при попытке изменения данных с помощью команды UPDATE.
· DELETE TRIGGER – запускаются при попытке удаления данных с помощью команды DELETE.
Конструкции [ DELETE] [,] [ INSERT] [,] [ UPDATE] и FOR | AFTER | INSTEAD OF } { [INSERT] [,] [UPDATE] определяют, на какую команду будет реагировать триггер. При его создании должна быть указана хотя бы одна команда. Допускается создание триггера, реагирующего на две или на все три команды.
Аргумент WITH APPEND позволяет создавать несколько триггеров каждого типа.
При создании триггера с аргументом NOT FOR REPLICATION запрещается его запуск во время выполнения модификации таблиц механизмами репликации.
Конструкция AS sql_оператор[...n] определяет набор SQL- операторов и команд, которые будут выполнены при запуске триггера.
Отметим, что внутри триггера не допускается выполнение ряда операций, таких, например, как:
· создание, изменение и удаление базы данных;
· восстановление резервной копии базы данных или журнала транзакций.
Выполнение этих команд не разрешено, так как они не могут быть отменены в случае отката транзакции, в которой выполняетсятриггер. Это запрещение вряд ли может каким-то образом сказаться на функциональности создаваемых триггеров. Трудно найти такую ситуацию, когда, например, после изменения строки таблицы потребуется выполнить восстановление резервной копии журнала транзакций.