Тема 9 Запросы на добавление, удаление и изменение записей. Работа с тригирами и транзакциями

Задание 1

Напишите запрос позволяющий добавлять записи в таблицу Группы, в качестве примера добавьте следующую информацию: код специальности: 230105, название группы: 15 тп, код аудитории: 75, классный руководитель: Утин Е.А., финансирование: Бюджетное.

Код запроса:

insert into Группы(код_специальности, группа, код_аудитории, классный_руководитель, финансирование)

values(230105,'15 тп',75,'Утин Е.А.','Бюджетное')

Задание 2

Командами Transact-SQL создайте копию таблицы Группы. Примечание: при создании копии ключи не создаются, для копии создайте первичный ключ.

Код запроса:

select * into Группы_2 from Группы

Alter table Группы_2 add primary key(код_группы)

Задание 3

Напишите запрос, который изменит финансирование у 15 тп группы на Комерческое.

Код запроса:

update Группы

set финансирование='Коммерческое'

where группа='15 тп'

Задание 4

Напишите запрос позволяющий у определенной группы изменять значение поля финансирование на противоположное. Примечание: используйте констукцию IF, предикат EXISTS, что бы по несколько раз не писать низвание группы, через DECLARE объявите переменную @G, присвойте ей значение (название группы) через команду SET.

Код запроса:

declare @G varchar(7)

set @G='15 тп'

if exists (select Группа from Группы where Группа=@G)

begin

if exists (select Группа from Группы where Группа=@G and финансирование='Бюджетное')

update Группы set финансирование='Коммерческое'

else

update Группы set финансирование='Бюджетное'

end

Задание 5

Напишите запрос, который пробегал по всем записям поля финансирование и удалял пробелы слева и справа.

Код запроса:

declare @i int

set @i=1

while @i<(select count(*) from Группы)

begin

update Группы set финансирование=rtrim(lTRIM('финансирование'))

select @i=@i+1

end

Задание 6

Напишите запрос, который циклически проверял все записи: существует ли аудитория, указанная в таблице Группы (в первом задании данной темы мы добавили запись с несуществующим кодом аудитории - 75), если существует аудитория – выводить сообщение: “Для записи с номером: № корректно установлена аудитория!”, если в таблице Аудитория нет записи, то в таблице Группы код аудитории изменить на нуль и вывести сообщение: “Для записи с номером: № установлена аудитория с номером 0!”. Примечание: используйте цикл WHILE, конструкцию IF, предикат EXISTS.

Код запроса:

DECLARE @C varchar(15)

SET @C=1

WHILE @C<(select count(*) from Группы)

BEGIN

SELECT @C=@C+1

if exists (select * from Группы, Аудитория where Группы.код_аудитории=Аудитория.код_аудитории)

Print 'Для записи с номером: '+@C+' корректно установлена аудитория!'

else

begin

Print 'Для записи с номером: '+@C+' установлена аудитория с номером 0!'

update Группы set код_аудитории=0

END

END

Задание 7

Напишите запрос позволяющий удалять записи из таблицы Группы, если код аудитории равен нулю.

Код запроса:

delete from Группы

where код_аудитории=0

Задание 8

Напишите триггер для таблицы Преподаватели, изменяя запись в поле ФИО необходимо чтобы срабатывал триггер UPDATE и изменял фамилии в таблицах: Аудитория, Группы, ПЦК.

Код запроса:

create trigger izm

on Преподаватели

for update as

declare @fn varchar(50),@fo varchar(50)

select @fn=ФИО from inserted

update Аудитория set руководитель=@fo where руководитель in (select ФИО from Deleted)

update Группы set классный_руководитель=@fo where классный_руководитель in (select ФИО from Deleted)

update пцк set председатель=@fo where председатель in (select ФИО from Deleted)

Задание 9

Создать триггер, если пытаются удалить преподавателя, запись которого используется в других таблицах, производить откат.

Код запроса:

create trigger t9

on Преподаватели

for delete

as declare @f varchar(50)

select @f=ФИО from Deleted

if exists (select руководитель from Аудитория where @f=руководитель

union select классный_руководитель from Группы where @f=классный_руководитель

union select председатель from пцк where @f=председатель)

print 'запись существует в таблице(ах)'

else delete from Преподаватели where @f=ФИО

Задание 10

Создать триггер, если пытаются удалить или изменить запись в таблице Группы, создавать резервную копию.

Код запроса:

create trigger t10

on Группы

for update, delete as

declare @fn varchar(50),@fo varchar(50)

select * into Группы_2

from Группы

Задание 11

Создать триггер удаления, если удаляемая дисциплина используется в учебном плане, произвести откат.

Код запроса:

create trigger t11

on Дисциплины

for delete

as declare @K varchar(50)

select @K=КОД from Deleted

if exists (select Код_дисциплины from Учебный_план where @K=Код_дисциплины)

print 'запись используется в таблице Учебный_план'

else delete from Дисциплины where @K=КОД

Задание 12

Создать триггер, который проверяет, если есть уже такая дисциплина в таблице, то ее не добавлять.

Код запроса:

create trigger t18

on Дисциплины

for insert

as declare @K varchar(50)

select @K=код from inserted

if exists (select полное from Дисциплины where @K=полное)

print 'Запись уже существует'

else delete from Дисциплины where @K=полное

Задание 13

Создать триггер, который проверяет, если при модификации полного названия дисциплины, окажется что такая дисциплина уже есть, то ее удалить.

Код запроса:

create trigger t19

on Дисциплины

for update

as declare @K varchar(50)

select @K=полное from updated

if exists (select полное from Дисциплины where @K=полное)

print 'Запись уже существует'

else delete from Дисциплины where @K=полное