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

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

CREATE TRIGGER tgrStudentDelete ON tblStudent AFTER DELETE

AS

DECLARE @idFaculty int, @TotalSum int, @idGroup int;

DECLARE DelCursor CURSOR FOR Select GroupId FROM Deleted; BEGIN

OPEN DelCursor;

FETCH NEXT FROM DelCursor INTO @idGroup; WHILE @@FETCH_STATUS = 0

BEGIN

Select @idFaculty=FacultyId From tblGroup Where GroupId=@idGroup; EXECUTE prStudentsOfFaculty @idFaculty, @TotalSum OUTPUT; UPDATE tblFaculty SET FacultyStudent=@TotalSum WHERE

FacultyId=@idFaculty;

FETCH NEXT FROM DelCursor INTO @idGroup; END;

CLOSE DelCursor; Deallocate DelCursor;

END

Триггер tgrStudentInsert вызывается для операций Insert и Update, а триггер tgrStudentDelete – для операции Delete. Работают они одинаково, единственное отличие заключается в том, что при добавлении записей используется таблица Inserted, а при удалении – Deleted, поэтому триггеры отличаются только запросом для курсора.

Для каждой изменяемой (добавляемой/удаляемой) записи триггер узнает идентификатор группу, в которую зачислен студент, по этому идентификатору находит идентификатор факультета, на котором учится студент, вызывает процедуру, которая подсчитывает общее количество студентов на факультете, и вносит правильное значение в соответствующую строку таблицы tblFaculty.

В принципе, все действия можно было бы реализовать и в одном триггере с использованием функции EXISTS(). Но тело триггера было бы в два раза длиннее и в нем были бы все команды из двух триггеров.

CREATE TRIGGER tgrPrint

ON tblDelivery

AFTER INSERT,DELETE,UPDATE

AS

BEGIN

IF EXISTS(Select * from Deleted)

PRINT 'Deleted';

IF EXISTS (Select * from Inserted)

PRINT 'Inserted';

END

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

21

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

Пример 5

Генерация уникального идентификатора аудитории.

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

CREATE TRIGGER tgrRoomInsert

ON tblRoom INSTEAD OF INSERT

AS

DECLARE @idRoom int, @RNumber char(6), @RCount int, @RBoard boolean, @RFloor int;

DECLARE InsCursor CURSOR FOR Select RoomNumber, PlaceCount, SmartBoard, RoomFloor FROM Inserted;

BEGIN

OPEN InsCursor;

FETCH NEXT FROM InsCursor INTO @RNumber, @RCount, @RBoard, @RFloor;

WHILE @@FETCH_STATUS = 0 BEGIN

SELECT @idRoom = max(RoomId) from tblRoom; SET @idRoom=@idRoom+1;

INSERT INTO tblRoom (RoomId, RoomNumber, PlaceCount, SmartBoard, RoomFloor) VALUES(@idRoom, @RNumber, @RCount, @RBoard, @RFloor)

FETCH NEXT FROM InsCursor INTO @RNumber, @RCount, @RBoard, @RFloor;

END;

CLOSE InsCursor; Deallocate InsCursor;

END

Пусть таблица tblRoom содержит следующие записи: tblRoom

	RoomId	RoomNumber	PlaceCount	SmartBoard	RoomFloor
	1	105	27	1	1
	3	201	14	0	2
	7	203	30	0	2
Конспект лекций по дисциплине «Базы данных» (Процедуры и триггеры)					22

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

Если пользователь попытается выполнить следующую команду

insert into tblRoom(RoomNumber, RoomFloor) values(‘327a’, 3), (‘403’, 4)

таблица tblRoom станет такой: tblRoom

RoomId	RoomNumber	PlaceCount	SmartBoard	RoomFloor
1	105	27	1	1
3	201	14	0	2
7	203	30	0	2
8	327a	Null	Null	3
9	403	Null	Null	4

Для двух добавленных записей будут сгенерированы значения поля RoomId: 8 и 9 соответственно. Так как пользователь не указал значения для полей PlaceCount и SmartBoard, то эти поля получили значения NULL.

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

23