Добавил:
Опубликованный материал нарушает ваши авторские права? Сообщите нам.
Вуз: Предмет: Файл:
Скачиваний:
47
Добавлен:
28.06.2014
Размер:
557.57 Кб
Скачать

МОСКОВСКИЙ ЭНЕРГЕТИЧЕСКИЙ ИНСТИТУТ (ТЕХНИЧЕСКИЙ УНИВЕРСИТЕТ)

ИНСТИТУТ АВТОМАТИКИ И ВЫЧИСЛИТЕЛЬНОЙ ТЕХНИКИ

КАФЕДРА ПРИКЛАДНОЙ МАТЕМАТИКИ

Лабораторная работа № 9.

Создание и использование триггеров.

Выполнили

студенты группы А-13-08

каф. Прикладной Математики

Буренков Сергей

Захаров Антон

Москва, 2011

Задание 1

Создать триггер на проверку правильности ввода значений в одно из полей вашей информационной таблицы: «Год приёма» (значения от ‘00’ до ‘99’), «Год окончания» (значения от ‘00’ до ‘99’), «Код категории обучения» (значения от ’01’ до ‘17’), «Код семейного положения» (значения от ‘0’ до ‘3’). Значения этих полей должны быть заданы в отдельной таблице вашей БД.

CREATE TABLE gp_values (cod char(2))

go

CREATE TABLE gok_values (cod char(2))

go

CREATE TABLE kat_obuch_cods (cod char(2))

go

CREATE TABLE family_cods (cod char(1))

go

INSERT INTO gp_values VALUES

('00'), ('01'), ('02'), ('03'), ('04'), ('05'), ('06'), ('07'), ('08'),

('09'), ('10'), ('11'), ('12'), ('13'), ('14'), ('15'), ('16'), ('17'),

('18'), ('19'), ('20'), ('21'), ('22'), ('23'), ('24'), ('25'), ('26'),

('27'), ('28'), ('29'), ('30'), ('31'), ('32'), ('33'), ('34'), ('35'),

('36'), ('37'), ('38'), ('39'), ('40'), ('41'), ('42'), ('43'), ('44'),

('45'), ('46'), ('47'), ('48'), ('49'), ('50'), ('51'), ('52'), ('53'),

('54'), ('55'), ('56'), ('57'), ('58'), ('59'), ('60'), ('61'), ('62'),

('63'), ('64'), ('65'), ('66'), ('67'), ('68'), ('69'), ('70'), ('71'),

('72'), ('73'), ('74'), ('75'), ('76'), ('77'), ('78'), ('79'), ('80'),

('81'), ('82'), ('83'), ('84'), ('85'), ('86'), ('87'), ('88'), ('89'),

('90'), ('91'), ('92'), ('93'), ('94'), ('95'), ('96'), ('97'), ('98'),

('99')

go

INSERT INTO gok_values VALUES

('00'), ('01'), ('02'), ('03'), ('04'), ('05'), ('06'), ('07'), ('08'),

('09'), ('10'), ('11'), ('12'), ('13'), ('14'), ('15'), ('16'), ('17'),

('18'), ('19'), ('20'), ('21'), ('22'), ('23'), ('24'), ('25'), ('26'),

('27'), ('28'), ('29'), ('30'), ('31'), ('32'), ('33'), ('34'), ('35'),

('36'), ('37'), ('38'), ('39'), ('40'), ('41'), ('42'), ('43'), ('44'),

('45'), ('46'), ('47'), ('48'), ('49'), ('50'), ('51'), ('52'), ('53'),

('54'), ('55'), ('56'), ('57'), ('58'), ('59'), ('60'), ('61'), ('62'),

('63'), ('64'), ('65'), ('66'), ('67'), ('68'), ('69'), ('70'), ('71'),

('72'), ('73'), ('74'), ('75'), ('76'), ('77'), ('78'), ('79'), ('80'),

('81'), ('82'), ('83'), ('84'), ('85'), ('86'), ('87'), ('88'), ('89'),

('90'), ('91'), ('92'), ('93'), ('94'), ('95'), ('96'), ('97'), ('98'),

('99')

go

INSERT INTO kat_obuch_cods VALUES

('01'), ('02'), ('03'), ('04'), ('05'), ('06'), ('07'), ('08'), ('09'),

('10'), ('11'), ('12'), ('13'), ('14'), ('15'), ('16'), ('17')

go

INSERT INTO family_cods VALUES ('0'), ('1'), ('2'), ('3')

go

CREATE TRIGGER lab9_tr1 ON arm FOR INSERT AS

BEGIN

IF(@@rowcount = 1)

BEGIN

DECLARE @gp char(2), @gok char(2), @kat char(2), @sem char(1)

DECLARE cur CURSOR FOR

SELECT gp, gok, kat_obuch_k, sem_polog_k FROM inserted

OPEN cur

FETCH cur into @gp, @gok, @kat, @sem

IF (SELECT count(*) FROM gp_values WHERE cod = @gp) = 0

BEGIN

PRINT 'ОШИБКА: Значение поля "Год приема" неверно!'

ROLLBACK TRAN

END

ELSE IF (SELECT count(*) FROM gok_values WHERE cod = @gok) = 0

BEGIN

PRINT 'ОШИБКА: Значение поля "Год окончания" неверно!'

ROLLBACK TRAN

END

ELSE IF (SELECT count(*) FROM kat_obuch_cods WHERE cod = @kat) = 0

BEGIN

PRINT 'ОШИБКА: Значение поля "Категория обучения" неверно!'

ROLLBACK TRAN

END

ELSE IF (SELECT count(*) FROM family_cods WHERE cod = @sem) = 0

BEGIN

PRINT 'ОШИБКА: Значение поля "Семейное положение" неверно!'

ROLLBACK TRAN

END

CLOSE cur

DEALLOCATE cur

END

END

Задание 2

Создать 3 отдельных триггера (для операторов INSERT, UPDATE и DELETE) для автоматического внесения изменений в таблицу VUZ (поля «Код вуза» и «Название вуза»), если вносятся такие же изменения в таблицу VUZ_GOROD.

CREATE TRIGGER lab9_tr2 ON vuz_gorod FOR INSERT AS

BEGIN

DECLARE @codvuza int, @nazvvuza char(25)

SELECT @codvuza = cod_vuza FROM inserted

SELECT @nazvvuza = nazvanie_vuza FROM inserted

INSERT vuz VALUES (@codvuza, @nazvvuza)

END

CREATE TRIGGER lab9_tr4 ON vuz_gorod FOR UPDATE AS

BEGIN

DECLARE @codvuza int, @codvuzanew int, @nazvvuza char(25), @nazvvuzanew char(25)

SELECT @codvuza = cod_vuza FROM deleted

SELECT @codvuzanew = cod_vuza FROM inserted

SELECT @nazvvuza = nazvanie_vuza FROM deleted

SELECT @nazvvuzanew = nazvanie_vuza from inserted

UPDATE vuz SET cod = @codvuzanew, uch_zavedenie = @nazvvuzanew

WHERE vuz.cod = @codvuza AND vuz.uch_zavedenie = @nazvvuza

END

CREATE TRIGGER lab9_tr3 ON vuz_gorod FOR DELETE AS

BEGIN

DECLARE @codvuza int, @nazvvuza char(25)

SELECT @codvuza = cod_vuza FROM deleted

SELECT @nazvvuza = nazvanie_vuza FROM deleted

DELETE FROM vuz WHERE cod = @codvuza AND uch_zavedenie = @nazvvuza

END

Задание 3

Создадим триггер, перемещающий соответствующих учащихся в архив (таблица «arm_arch») при удалении записи о каком-либо вузе из таблицы «vuz».

CREATE TABLE arm_arch

(

nomer integer,

fio char (40) NOT NULL,

data_rogden char(6),

pol char(1),

sem_polog_k char(1),

spec char(6),

kat_obuch_k char(2),

mp char(2),

gp char(2),

mo char(2),

gok char(2)

)

go

CREATE TRIGGER lab9_tr4 ON vuz FOR DELETE AS

BEGIN

DECLARE @cod int, @nomer int, @fio char(40), @data_rogden char(6),

@pol char(1), @sem_polog_k char(1), @spec char(6),

@kat_obuch_k char(2), @mp char(2), @gp char(2), @mo char(2),

@gok char(2)

SELECT @cod = cod FROM deleted

DECLARE cur CURSOR FOR

SELECT nomer, fio, data_rogden, pol, sem_polog_k, spec, kat_obuch_k, mp, gp, mo, gok FROM arm WHERE vuz_k = @cod

OPEN cur

FETCH cur INTO @nomer, @fio, @data_rogden, @pol, @sem_polog_k, @spec,

@kat_obuch_k, @mp, @gp, @mo, @gok

INSERT INTO arm_arch VALUES (@nomer, @fio, @data_rogden, @pol, @sem_polog_k,

@spec, @kat_obuch_k, @mp, @gp, @mo, @gok)

WHILE(@@fetch_status = 0)

BEGIN

FETCH cur into @nomer, @fio, @data_rogden, @pol, @sem_polog_k, @spec,

@kat_obuch_k, @mp, @gp, @mo, @gok

INSERT INTO arm_arch VALUES (@nomer, @fio, @data_rogden, @pol,

@sem_polog_k, @spec, @kat_obuch_k, @mp, @gp, @mo, @gok)

IF(@@fetch_status = -1) BREAK

END

DELETE FROM arm WHERE vuz_k = @cod

DELETE FROM vuz WHERE cod = @cod

END

Результат:

DELETE FROM vuz WHERE cod = 1855652

go

SELECT * FROM arm_arch