Преобразовать представления в хранимые процедуры
Создадим хранимую процедуру для созданного представления. Потребуется sql-код сгенерированного в представлении.
Этапы создания:
Создадим новую хранимую процедуру.
В созданной новой хранимой процедуре нужно указать ее имя.
Укажите нужные входные переменные (для данного представления не нужны входные данные).
Вставим sql-код сгенерированного в представлении.
Хранимая процедура.
CREATE PROCEDURE [dbo].[sample1]
AS
SELECT LastName AS Фамилия, BirthDate AS [День рождения] FROM [dbo].[Employees]
WHERE (BirthDate > CONVERT(DATETIME, '1930-01-01 00:00:00', 102))
ORDER BY Фамилия
Выполним созданную процедуру: exec sample1
Результат работы хранимой процедуры:
Рисунок 5.5 – Результат выполнения хранимой процедуры
Теперь хранимую процедуру можно использовать в клиентском приложении.
Создание триггеров
Триггер ‑ это специальный тип хранимой процедуры, которая запускается автоматически системой SQL Server при модифицировании какой-либо таблицы одним из трех операторов: UPDATE, INSERT или DELETE.
Триггеры, как другие хранимые процедуры, могут содержать простые или сложные операторы T-SQL. В отличие от других типов хранимых процедур триггеры запускаются автоматически при указанных модификациях данных; их нельзя запустить вручную по имени. Когда происходит запуск триггера, говорят, что он активизируется.
Триггер создается по одной таблице базы данных, но он может осуществлять доступ и к другим таблицам и объектам других баз данных. Триггеры нельзя создать по временным таблицам или системным таблицам, а только по определенным пользователем таблицам или представлениям. Таблица, по которой определяется триггер, называется таблицей триггера.
Существует пять типов триггеров: UPDATE, INSERT, DELETE, INSTEAD OF и AFTER. Как следует из названий, триггер UPDATE активизируется, когда выполняются изменения (обновления) в какой-либо таблице, триггер INSERT активизируется, когда происходит вставка данных в таблицу и триггер DELETE активизируется, когда из таблицы удаляются данные. Триггер INSTEAD OF выполняется вместо операции вставки, обновления или удаления. Триггер AFTER активизируется после какой-либо запускающей операции и обеспечивает механизм управления порядком выполнения нескольких триггеров.
Операции обновления, вставки и удаления называются событиями модификации данных. Вы можете создать триггер, который активизируется при возникновении более чем одного события модификации данных.
Правила:
Триггеры запускаются только после завершения оператора, который вызвал их активизацию. Например, UPDATE-триггер не будет активизироваться, пока не будет выполнен оператор UPDATE.
Если какой-либо оператор пытается выполнить операцию, которая нарушает какое-либо ограничение по таблице или является причиной какой-то другой ошибки, то связанный с ним триггер не будет активизирован.
Триггер рассматривается как часть одной транзакции вместе с оператором, который вызывает его. Поэтому из триггера можно вызвать оператор отката, и этот оператор выполнит откат как триггера, так и соответствующего события модификации данных. Кроме того, при возникновении серьезной ошибки, такой как разъединение с пользователем, SQL Server автоматически выполнит откат всей транзакции.
Триггер активизируется только один раз для одного оператора, даже если этот оператор влияет на несколько строк данных.
При активизации триггера результаты (если они есть) возвращаются вызывающей программе, как и при использовании хранимых процедур. Обычно результаты не возвращаются из оператора INSERT, UPDATE или DELETE (это операторы, вызывающие активизацию триггера).
Триггеры, как и ограничения, можно использовать для поддержки целостности данных и деловых правил, но триггер не следует использовать как замену ограничения, когда достаточно использовать только ограничение.
Не нужно создавать триггер, который проверяет наличие значения в колонке первичного ключа одной таблицы, чтобы определить, можно ли вставить это значение в соответствующую колонку другой таблицы; в этой ситуации прекрасно подойдет ограничение FOREIGN KEY. Может потребоваться триггер для каскадирования изменений, вносимых в связанные таблицы базы данных.
Синтаксис оператора CREATE TRIGGER:
CREATE TRIGGER имя_триггера
ON (таблица │ представление)
[WITH ENCRYPTION] {FOR | AFTER | INSTEAD OF}
{[DELETE] [,] [INSERT] [,] [UPDATE]}
[WITH APPEND]
[NOT FOR REPLICATION] AS
oператор_sql[1...n]
Можете создать триггер для оператора INSERT, UPDATE, DELETE, INSTEAD OF или AFTER или для любой комбинации из этих пяти операторов. Должны задать хотя бы одну опцию с предложением FOR. Это предложение указывает, возникновение какого типа события модификации данных (или типов событий) по указанной таблице приведет к активизации данного триггера.
При вызове триггера будут выполнены операторы SQL, указанные после ключевого слова AS. Вы можете поместить сюда несколько операторов, включая программные конструкции, такие как IF и WHILE. В определении триггера не допускаются следующие операторы:
ALTER DATABASE
CREATE DATABASE
DROP DATABASE
LOAD DATABASE и др.
Триггеры для добавления данных. В случае вставки записи c пустым идентификатором триггер выдает ошибку. Транзакция откатывается. (рисунок 5.6)
Рисунок 5.6 – Триггер на вставку
Будет возвращено сообщение «Недопустимый идентификатор»
Триггеры на удаление. Данные триггеры позволяют пoддеpживaть целостность базы. Триггеры проверяют ссылки на связанные таблицы. Если на удаляемую запись есть ссылка из другой таблицы, выводится ошибка. Транзакция откатывается. (рисунок 5.7).
Рисунок 5.7 – Триггер на удаление
Будет возвращено сообщение «Присутствуют ссылки на удаляемую запись»
Paбoтa триггера для обновления данных аналогична работе триггера на вставку (рисунок 5.8).
Рисунок 5.8 – Триггер на обновление
Будет возвращено сообщение «Недопустимый иденетификатор».
Содержание отчета
Отчет по лабораторной работе должен содержать:
цель работы;
постановка задачи;
представление с описанием
исходный код хранимых процедур с комментариями;
исходный код триггера с примером работы;
выводы;
список литературы.