Архив / Пособие_Триггеры и процедуры (MS SQL Server)

ПетрГУ, кафедра прикладной математики и кибернетики

Конспект лекций по дисциплине «Базы данных»

Тема: Процедуры и триггеры

(на примере MS SQL Server)

составитель: Л. В. Щёголева

Конспект лекций по дисциплине «Базы данных» (Процедуры и триггеры)

1

ПетрГУ, кафедра прикладной математики и кибернетики

СОДЕРЖАНИЕ

Введение .........................................................................................................		3
1	Описание структуры базы данных............................................................	4
2	Понятие процедуры ....................................................................................	6
3	Команды работы с процедурами ...............................................................	7
4	Понятие триггера ........................................................................................	8
5	Команды работы с триггерами ..................................................................	9
6	Примеры реализации триггеров ..............................................................	10
	Пример 1...............................................................................................	10
	Пример 2...............................................................................................	16
	Пример 3...............................................................................................	19
	Пример 4...............................................................................................	20
	Пример 5...............................................................................................	22

Конспект лекций по дисциплине «Базы данных» (Процедуры и триггеры)

2

ПетрГУ, кафедра прикладной математики и кибернетики

Введение

Внастоящем пособии представлены примеры команд создания процедур и триггеров

сописанием их работы.

Все команды написаны в синтаксисе MS SQL Server.

Примеры приведены для базы данных, описание структуры которой представлено в

разделе 1.

Конспект лекций по дисциплине «Базы данных» (Процедуры и триггеры)

3

ПетрГУ, кафедра прикладной математики и кибернетики

1 Описание структуры базы данных

Таблица tblFaculty содержит информацию о факультетах университета.

tblFaculty

Наименование	Домен	Ключ	Описание
атрибута
FacultyId	Integer	PK	Идентификатор факультета
FacultyName	Char(100)		Название факультета
DeenName	Char(50)		ФИО декана
FacultyRoomId	Integer	FK(tblRoom)	Номер кабинета деканата
FacultyPhone	Char(6)		Телефон деканата
FacultyStudent	Integer		Количество студентов факультета

Таблица tblStudent содержит информацию о студентах университета в одном учебном году.

tblStudent

Наименование атрибута	Домен	Ключ	Описание
StudentId	Integer	PK	Идентификатор студента
StudentName	Char(100)		ФИО студента
GroupId	Integer	FK(tblGroup)	Группа
Grant	Decimal(6,0)		Стипендия

Таблица tblGroup содержит информацию о студенческих группах университета в одном учебном году.

tblGroup

Наименование атрибута	Домен	Ключ	Описание
GroupId	Integer	PK	Идентификатор группы
GroupHead	Integer	FK(tblStudent)	Староста
GroupNumber	Char(10)		Номер группы
FacultyId	Integer	FK(tblFaculty)	Факультет
StudyYear	Integer		Курс

Таблица tblSubject содержит информацию об изучаемых студентами факультета дисциплинах в одном учебном году.

	tblSubject
	Наименование атрибута	Домен	Ключ	Описание
	SubjectId	Integer	PK	Идентификатор предмета
	SubjectName	Char(40)		Название предмета
	LecturesHour	Integer		Количество часов лекций
	PracticeHour	Integer		Количество часов практик
	FacultyId	Integer	FK(tblFaculty)	Факультет
	Конспект лекций по дисциплине «Базы данных» (Процедуры и триггеры)				4

ПетрГУ, кафедра прикладной математики и кибернетики

Таблица tblRoom содержит информацию об аудиториях университета.

tblRoom

Наименование атрибута	Домен	Ключ	Описание
RoomId	Integer	PK	Идентификатор аудитории
RoomNumber	Char(6)		Номер аудитории
PlaceCount	Integer		Количество рабочих мест
SmartBoard	Boolean		Наличие электронной доски
RoomFloor	Integer		Этаж

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

tblSchedule

Наименование атрибута	Домен	Ключ	Описание
ScheduleId	Integer	PK	Идентификатор
RoomId	Integer	FK(tblRoom)	Аудитория
Weekday	Char(15)		День недели
Time	Char(15)		Время
SubjectId	Integer	FK(tblSubject)	Предмет
GroupId	Integer	FK(tblGroup)	Группа
TeacherName	Char(60)		ФИО преподавателя

Конспект лекций по дисциплине «Базы данных» (Процедуры и триггеры)

5

ПетрГУ, кафедра прикладной математики и кибернетики

2 Понятие процедуры

Процедура представляет собой программу, написанную на внутреннем языке СУБД, хранящуюся в базе данных в виде самостоятельного объекта. Такие процедуры обычно называются хранимыми, присоединенными. Процедуры могут быть вызваны прикладной программой. Процедуры исполняются на сервере базы данных. Процедуры могут содержать параметры и возвращать значения, в том числе и сообщения об ошибке.

Преимущества использования процедур:

–централизованный контроль доступа к данным;

–прикладные программы могут вызывать процедуру, что сокращает время написания программ, при модификации процедуры, все вызывающие ее программы получат новый код, оптимизация кода;

–снижает трафик в сети в системах «клиент-сервер» за счет передачи только имени процедуры и ее параметров вместо обмена данными, а процедура выполняется на сервере;

–сокрытие от пользователя многих особенностей конкретного устройства базы данных, что обеспечивает большую независимость данных;

–большая безопасность данных, пользователь может иметь право вызывать

процедуру, но не управлять данными, которые вызываются этой процедурой; Недостаток: отсутствие стандартов в реализации процедур.

Конспект лекций по дисциплине «Базы данных» (Процедуры и триггеры)

6

ПетрГУ, кафедра прикладной математики и кибернетики

3 Команды работы с процедурами в MS SQL Server

Создание процедуры

CREATE PROCEDURE <имя процедуры>

[@<имя параметра> <тип данных> [OUTPUT ], ...]

AS

[DECLARE @<имя переменной> <тип данных>, ...] BEGIN

<операторы>

END

Имена всех переменных в MS SQL Server должны начинаться с символа

«@»

Вызов процедуры

EXECUTE <имя процедуры> [{@<имя переменной> | <значение параметра>}[OUTPUT ], ...]

Удаление процедуры

DROP PROCEDURE <имя процедуры>

Пример

Процедура подсчитывает количество студентов, обучающихся на факультете, идентификатор которого является входным параметром процедуры @id, и возвращает это значение в параметре @total_sum.

Create Procedure prStudentsOfFaculty @id int, @total_sum int output AS

Begin

Set @total_sum = 0

Set @total_sum = (Select count(*) From tblStudent, tblGroup Where (tblStudent.GroupId = tblGroup.GroupId) and (tblGroup.FacultyId = @id)) End

Конспект лекций по дисциплине «Базы данных» (Процедуры и триггеры)

7

ПетрГУ, кафедра прикладной математики и кибернетики

4 Понятие триггера

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

Особенности реализации триггеров в MS SQL Server

ВMS SQL Server:

–триггер может быть вызван либо после выполнения операции, либо вместо выполнения операции;

–триггер вызывается один раз для всех записей таблицы, над которыми должна быть выполнена операция;

–следовательно, изменяемые записи хранятся в двух автоматически создаваемых при вызове триггера таблицах:

o таблица Inserted – содержит измененные или добавленные записи таблицы;

o таблица Deleted – содержит записи до выполнения изменений или удаленные записи таблицы;

–в теле триггера, определенного для операции Insert, доступна только таблица

Inserted;

–в теле триггера, определенного для операции Delete, доступна только таблица

Deleted;

–в теле триггера, определенного для операции Update, доступны обе таблицы

Inserted и Deleted;

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

Конспект лекций по дисциплине «Базы данных» (Процедуры и триггеры)

8

ПетрГУ, кафедра прикладной математики и кибернетики

5 Команды работы с триггерами

Создание

CREATE TRIGGER <имя триггера> ON <имя таблицы>

{ FOR | AFTER | INSTEAD OF }

[ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] AS

DECLARE @<имя переменной> <тип данных>, ...

BEGIN <операторы>

END

Удаление

DROP TRIGGER <имя триггера>

Конспект лекций по дисциплине «Базы данных» (Процедуры и триггеры)

9

ПетрГУ, кафедра прикладной математики и кибернетики

6 Примеры реализации триггеров

Пример 1

Ограничение предметной области: стипендия студента не может быть увеличена более чем на 5% от предыдущей стипендии.

CREATE TRIGGER tgrStudentGrantUpdate

ON tblStudent AFTER UPDATE

AS

DECLARE @Grant_old float, @Grant_new float, @Id int;

BEGIN

Select @Grant_old = Grant from Deleted

Select @Grant_new = Grant, @Id = StudentId from Inserted

IF (@Grant_new - @Grant_old > 0.05 * @Grant_old)

UPDATE tblStudent SET Grant = 1.05 * @Grant_old

WHERE StudentId = @Id

END

Триггер tgrStudentGrantUpdate создан для таблицы tblStudent. Триггер будет срабатывать после выполнения операции изменения данных.

Втриггере определены три локальные переменные: @Grant_old (вещественного типа) для хранения старой стипендии студента, @Grant_new (вещественного типа) для хранения новой стипендии студента, @Id (целого типа) для хранения идентификатора студента.

При вызове триггера СУБД создает две таблицы: Deleted, содержащую измененные записи до их изменения, и Inserted, содержащую измененные записи после их изменения.

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

Далее, в теле триггера проверяется условие о величине изменения стипендии. Если стипендия изменилась более чем на 5%, то триггер вносит поправку в данные – увеличивает стипендию только на 5% по сравнению со предыдущим значением стипендии студента. Это действие выполняется посредством вызова операции Update в таблице tblStudent для соответствующего студента.

Конспект лекций по дисциплине «Базы данных» (Процедуры и триггеры)

10