Пример 1: Напишите хранимую процедуру для вывода информации о сервере, о базе данных и о текущем пользователе, и вызовите ее:
CREATE PROC Пример1 AS
BEGIN
SELECT
@@Servername AS Сервер
,@@Version AS [Версия СУБД]
,Db_Name() AS [База данных]
,User AS [Пользователь базы данных]
,System_User AS [Системный пользователь]
END
EXECUTE Пример1
стран:
Пример 2: Напишите хранимую процедуру, которая выводит названия и столицы всех
CREATE PROC Пример2 AS
BEGIN
SELECT
Название
, Столица
END
FROM
Страны
Пример 3: Напишите хранимую процедуру, которая выводит список стран заданной части света, и вызовите ее:
CREATE PROC Пример3
@Конт AS VARCHAR(50)
AS BEGIN
SELECT
Название
,Столица
,Площадь
,Население
FROM
Страны
WHERE
Континент = @Конт
END
EXECUTE Пример3 'Азия'
Пример 4: Напишите хранимую процедуру, которая выводит список стран, площадь которых находится в заданном интервале, и вызовите ее:
CREATE PROC Пример4 @A AS FLOAT, @B AS FLOAT
AS BEGIN
SELECT
Название
,Столица
,Площадь
,Население
,Континент
FROM
Страны
WHERE
Площадь BETWEEN @A AND @B
END
EXECUTE Пример4 1000, 10000
Пример 5: Напишите хранимую процедуру, которая возвращает количество стран, со- держащих в названии заданную букву, и вызовите ее:
CREATE PROC Пример5
@Буква AS CHAR(1), @Количество AS INT OUTPUT
AS BEGIN
SELECT
@Количество = COUNT(*)
FROM
Страны
WHERE
CHARINDEX(@Буква, Название) > 0
END
DECLARE @К AS INT DECLARE @Б AS CHAR(1) SET @Б = 'у'
EXECUTE Пример5 @Б, @К OUTPUT SELECT
@К AS [Количество стран]
Пример 6: Напишите хранимую процедуру для вывода трех стран с наименьшей пло- щадью в заданной части света, и вызовите ее. Если часть света не указана, выбрать Европу:
CREATE PROC Пример6
@Конт AS VARCHAR(50) = 'Европа'
AS BEGIN
SELECT TOP 3
Название
,Столица
,Площадь
,Население
,Континент
FROM
Страны WHERE
Континент = @Конт ORDER BY
Площадь
END
EXECUTE Пример6 DEFAULT
Пример 7: Напишите хранимую процедуру, которая создает таблицу «Страны_У», и заполняет ее странами, названия которых начинаются на букву «У»:
CREATE PROC Пример7 AS
BEGIN
SELECT
Название
,Столица
,Площадь
,Население
,Континент
INTO FROM
Страны_У Страны
WHERE
LEFT(Название, 1) = 'У'
END
EXECUTE Пример7
Пример 8: Напишите хранимую процедуру, которая удаляет таблицу «Страны_У» и возвращает количество строк:
CREATE PROC Пример8 AS
BEGIN
DECLARE @K AS INT
SELECT
@K = COUNT(*)
FROM
Страны_У
DROP TABLE Страны_У RETURN @K
END
DECLARE @C AS INT
EXECUTE @C = Пример8
SELECT @C AS [Количество строк в удаленной таблице]
Пример 9: Напишите код, который удаляет хранимую процедуру «Пример8»: DROP PROC Пример8
Задание
Напишите хранимую процедуру для вывода информации о сервере, о базе данных, о текущем пользователе, о текущем времени, и вызовите ее.
Напишите хранимую процедуру, которая выводит данные всех стран.
Напишите хранимую процедуру, которая выводит список стран, кроме заданной части света, и вызовите ее.
Напишите хранимую процедуру, которая выводит список стран, население кото- рых находится в заданном интервале, и вызовите ее.
Напишите хранимую процедуру, которая возвращает количество стран, у которых в названии отсутствует заданная буква, и вызовите ее.
Напишите хранимую процедуру для вывода пяти стран с наибольшим населением в заданной части света, и вызовите ее. Если часть света не указана, выбрать Африку.
Напишите хранимую процедуру, которая создает таблицу «Страны_<первая буква вашей фамилии>», и заполняет ее странами, названия которых начинаются с первой буквой вашей фамилии.
Напишите хранимую процедуру, которая удаляет таблицу, которую вы создали в предыдущем задании и возвращает количество удаленных строк.
Напишите хранимую процедуру, принимающую число и возвращающую количе- ство цифр в нем через параметр OUTPUT.
Напишите хранимую процедуру AddRightDigit, добавляющую к целому положи- тельному числу K справа цифру D (D - входной параметр целого типа, лежащий в диапазоне [0..9], K - параметр целого типа, являющийся одновременно входным и выходным).
Напишите хранимую процедуру InvDigit, меняющую порядок следования цифр це- лого положительного числа K на обратный (K - параметр целого типа, являющийся одновре- менно входным и выходным).
Напишите хранимую процедуру Swap, меняющую содержимое переменных X и Y (X и Y - вещественные параметры, являющиеся одновременно входными и выходными).
Напишите хранимую процедуру SortInc, меняющую содержимое переменных A, B, C, таким образом, чтобы их значения оказались упорядоченными по возрастанию (A, B, C
- вещественные параметры, являющиеся одновременно входными и выходными).
Напишите хранимую процедуру DigitCountSum, находящую количество C цифр целого положительного числа K, а также их сумму S (K - входной, C, S - выходные параметры целого типа).
Напишите код, который удаляет все хранимые процедуры, вами созданные.
Лабораторная работа 11
Триггеры
Цель работы
Изучить создание триггеров.
Изучить триггеры после событий.
Изучить триггеры вместо событий.
Изучить виртуальные таблицы в триггерах.
Изучить приостановление триггеров.
Изучить удаление триггеров.
Теоретическая часть
Триггер - это вид хранимой процедуры, который вызывается автоматически при опре- деленных событиях. Часто триггеры применяются для автоматической поддержки целостно- сти и защиты БД.
В MS SQL Server существует три вида триггеров, которые отличаются по функциям и по синтаксису создания и изменения:
Триггеры DML вызываются при выполнении команд INSERT, UPDATE или DELETE. Можно создать триггер, реагирующий на две или на все три команды.
Триггеры DDL реагируют на события изменения структуры БД: создание, изменение или удаление отдельных объектов БД.
Триггеры входа в систему запускаются при соединении пользователя с экземпляром сервера. Их можно применять для дополнительной проверки полномочий пользователей.
Триггеры DML можно вызвать после событий (FOR | AFTER), или вместо него (INSTEAD OF).
Триггер AFTER выполняется после успешного завершения вызвавшего его события. Можно определить несколько АFТЕR-триггеров для каждой операции. Триггер INSTEAD OF вызывается вместо выполнения команд. Для каждой операции INSERT, UPDATE, DELETE можно определить только один INSTEAD ОF-триггер.
Упрощенный синтаксис создания триггера имеет следующий вид:
CREATE TRIGGER <название триггера> ON <название таблицы>
<FOR | AFTER | INSTEAD OF> <INSERT | UPDATE | DELETE> AS
[BEGIN] <команды> [END]
Ключевое слово FOR или AFTER указывает, что триггер DML срабатывает только по- сле успешного запуска всех операций в инструкции SQL, по которой срабатывает триггер.
Ключевое слово INSTEAD OF указывает, что триггер DML выполняется вместо ин- струкции SQL, по которой он срабатывает, то есть переопределяет действия запускающих ин- струкций.
В определении триггера ключевые слова INSERT | UPDATE | DELETE определяют ин- струкции изменения данных, при применении которых к таблице или представлению сраба- тывает триггер DML. Указание хотя бы одного варианта обязательно. В определении триггера разрешены любые сочетания вариантов в любом порядке.
Триггеры не вызываются рекурсивно.
Хотя инструкция TRUNCATE TABLE по сути аналогичная инструкции DELETE, она не активирует триггер.
Если триггер выполняется для события добавления данных (команды INSERT), в теле триггера доступна виртуальная таблица INSERTED, которая содержит список добавленных данных.
Если триггер выполняется для события удаления данных (команды DELETE), в теле триггера доступна виртуальная таблица DELETED, которая содержит список удаленных дан- ных.
Если триггер выполняется для события изменения данных (команды UPDATE), в теле триггера доступны две виртуальные таблицы INSERTED и DELETED, которые содержат спи- сок новых и старых данных, соответственно.
Если при определенных обстоятельствах выполнение триггера нежелательно, то можно его отключить. Для этого используется команда DISABLE TRIGGER, его синтаксис:
DISABLE TRIGGER <название триггера> ON <название таблицы>
А когда триггер снова понадобится, его можно включить с помощью команды ENABLE TRIGGER, его синтаксис:
ENABLE TRIGGER <название триггера> ON <название таблицы>
Для удаления триггера используется команда DROP TRIGGER, его синтаксис: DROP TRIGGER <название триггера>
Практическая часть
Дана таблица Ученики:
|
ID |
Фамилия |
Предмет |
Школа |
Баллы |
|
|
1 |
Иванова |
Математика |
Лицей |
98,5 |
|
|
2 |
Петров |
Физика |
Лицей |
99 |
|
|
3 |
Сидоров |
Математика |
Лицей |
88 |
|
|
4 |
Полухина |
Физика |
Гимназия |
78 |
|
|
ID |
Фамилия |
Предмет |
Школа |
Баллы |
|
|
5 |
Матвеева |
Химия |
Лицей |
92 |
|
|
6 |
Касимов |
Химия |
Гимназия |
68 |
|
|
7 |
Нурулин |
Математика |
Гимназия |
81 |
|
|
8 |
Авдеев |
Физика |
Лицей |
87 |
|
|
9 |
Никитина |
Химия |
Лицей |
94 |
Пример 1: Напишите триггер на добавление записи в таблицу «Ученики». Данный триггер, в случае успешного добавления данных, выводит «Запись добавлена»:
CREATE TRIGGER Пример1 ON Ученики FOR INSERT
AS BEGIN
PRINT 'Запись добавлена'
END
Пример 2: Напишите триггер на удаление записи из таблицы «Ученики». Данный триг- гер, в случае успешного удаления данных, выводит «Запись удалена»:
CREATE TRIGGER Пример2 ON Ученики AFTER DELETE
AS BEGIN
PRINT 'Запись удалена'
END
Пример 3: Напишите триггер на добавление, изменение и удаление данных для таб- лицы «Ученики». Данный триггер выводит «Таблица изменена»:
CREATE TRIGGER Пример3 ON Ученики FOR INSERT, UPDATE, DELETE
AS BEGIN
PRINT 'Таблица изменена'
END
Пример 4: Напишите триггер на удаление записи из таблицы «Ученики». Данный триг- гер, при попытке удаления данных, выводит «Нельзя удалить данные»:
CREATE TRIGGER Пример4 ON Ученики INSTEAD OF DELETE
AS BEGIN
PRINT 'Нельзя удалить данные'
END
Пример 5: Создать таблицу «Ученики_Архив», которая будет содержать все данные об удаленных учениках и даты их удаления. Написать триггер, который будет фиксировать в таб- лице «Ученики_Архив» данные ученика, удаленного из таблицы «Ученики»:
CREATE TABLE Ученики_Архив (
ID INT NOT NULL,
Фамилия VARCHAR(50) NULL, Предмет VARCHAR(50) NULL, Школа VARCHAR(50) NULL, Баллы FLOAT NULL,
Удалено DATETIME NOT NULL
)
CREATE TRIGGER Пример5 ON Ученики FOR DELETE
AS BEGIN
INSERT
Ученики_Архив SELECT
ID,
Фамилия, Предмет, Школа, Баллы,
GETDATE() AS Удалено
END
FROM
DELETED
Пример 6: Напишите команды для приостановления и запуска триггера из примера 5: DISABLE TRIGGER Пример5 ON Ученики
ENABLE TRIGGER Пример5 ON Ученики
Пример 7: Напишите команды для удаления триггера из примера 5: DROP TRIGGER Пример5
Задание
Напишите триггер на изменение записи в таблице «Ученики». Данный триггер, в случае изменения данных, должен вывести «Запись изменена».
Напишите триггер на добавление и удаление записи из таблицы «Ученики». Данный триггер, в случае успешного добавления или удаления данных, должен вывести «Количество строк изменено».
Напишите триггер на добавление, изменение и удаление данных в таблице «Уче- ники». Данный триггер должен вывести «{Текущий пользователь} изменил таблицу. Время:
{текущее время}».
Напишите триггер на изменение записи в таблице «Ученики». Данный триггер, при попытке изменения данных, должен вывести «Нельзя редактировать данные».
Создать таблицу «Ученики_{Ваша_фамилия}», которая будет содержать фамилии удаленных учеников и даты их удаления. Написать триггер, который будет фиксировать в таб- лице «Ученики_{Ваша_фамилия}» данные учеников при удалении из таблицы «Ученики», в том случае, если у них остались однофамильцы в таблице «Ученики».
Напишите команды для приостановления и запуска триггера из предыдущей задачи.
Напишите команды для удаления всех созданных вами триггеров.
Лабораторная работа № 15