Лабораторные работы (2011) / Буренков, Захаров / Лабораторная работа 9
.docМОСКОВСКИЙ ЭНЕРГЕТИЧЕСКИЙ ИНСТИТУТ (ТЕХНИЧЕСКИЙ УНИВЕРСИТЕТ)
ИНСТИТУТ АВТОМАТИКИ И ВЫЧИСЛИТЕЛЬНОЙ ТЕХНИКИ
КАФЕДРА ПРИКЛАДНОЙ МАТЕМАТИКИ
Лабораторная работа № 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