存储过程和触发器

存储过程和触发器管理

实验报告

学院：信息学院班级：电子信息工程

学号：20091060253 姓名：郑璇成绩：

【实验目的】

1．学习创建存储过程。

2．学习创建管理触发器。

【实验内容】

1.在pubs数据库中创建存储过程avg_price，用于求所有出版图书的平均单价。并通过输出参数返回该平均单价。要求在创建过程之前要首先判断该存储过程是否已经存在，如果存在，则将其删除。执行存储过程，打印图书平均单价。

if exists (select name from sysobjects

where name='avg_price' and type='p')

drop procedure avg_price

go

create procedure avg_price

@avgprice float output

as

select @avgprice=avg(price) from titles

go

declare @avgprice float

exec avg_price @avgprice output

print @avgprice

3．在pubs数据库中创建存储过程max_price，根据存储过程的图书类型（输入参数）返回该类图书的最高单价（输出参数）。要求在创建过程之前要首先判断该存储过程是否已经存在，如果存在，则将其删除。

if exists(select name from sysobjects where name='max_price' and type='p')

drop procedure max_price

go

create procedure max_price

@type char(50),

@max float output

as

select @max=max(price) from titles where type=@type

go

4．执行第三题创建的存储过程max_price,指定图书类型为“mod_cook”,打印该类型图书的最高单价。

declare @max float

exec max_price'mod_cook',@max output

print @max

5．删除存储过程avg_price,max_price。

drop procedure avg_price

drop procedure max_price

二．触发器

创建一个“学生信息”数据库，包含“学生基本信息”表、“专业”表和“系”表，各表包含的字段如下。

●“学生基本信息”表：学号；性别；班级；出生日期；专业编号。

●“专业”表：专业编号；专业名称；系编号。

●“系”表：系编号；系名称；系简介。

各字段类型按其实际含义自行定义，输入一些数据，要求数据要有代表性。

以下操作要求全部在查询分析器中完成，保存或记录实现各题功能的Transcat-SQL语

句。

1.在“专业”表上创建一个INSERT触发器“TRG1”。当发生插入专业表操作时，

将显示插入的记录。

2.在“专业”表表上创建一个DELETE触发器“TRG2”。当发生删除操作时，将

给出警告、列出删除的记录并撤消删除。

3.在“专业”表表上创建一个UPDA TE触发器“TRG3”。当发生更新“专业名称”

字段的操作时，给出警告并撤消更新。

if exists(select name from sysobjects

where name='trg3' and type='tr')

drop trigger trg3

go

create trigger trg3 on 专业

for update

as

if update(专业名称)

begin

print '不允许更新专业名称！'

rollback transaction

end

else

print '没有更新专业名称，不需要触发器处理'

go

update 专业set 专业名称='新专业A' where 专业名称='新专业'

4.在“学生基本信息”表上创建一个更新触发器“TRG4”，当发生更新“学号”

或“姓名”字段的操作时给出警告，并撤消更新。

drop trigger trg4

go

create trigger trg4 on 学生基本信息

for update

as

if update(学号) or update(姓名)

begin

print '不允许更新学号或姓名'

rollback transaction

end

else

print'没有更新学号或姓名，不需要触发器处理'

go

update 学生基本信息set 学号='2002090261' where 学号='2002090161'

GO

update 学生基本信息set 姓名='张三' where 学号='2002090161'

5.创建一个视图view1，view1中包含了某班级的学生基本信息的以下字段：学

号；姓名；性别；出生日期。现在要通过该视图向该班级添加新的学生信息，在该视图上创建一个INSTEAD OF触发器“TRG5”，使得添加到“学生基本信息”表中的记录内容完整。

create view view1

as

select 学号,姓名,性别,出生日期

from 学生基本信息

go

if exists(select name from sysobjects

where name='trg5'and type='tr')

drop trigger trg5

go

create trigger trg5 on view1

instead of insert

as

declare @num char(11)

declare @name char(10)

declare @sex bit

declare @birth datetime

select @num=学号,@name=姓名,@sex=性别,@birth=出生日期from inserted

insert into 学生基本信息

values(@num,@name,@sex,'二班',@birth,1)

go

insert into view1

values('2002060153','李梅',0,1981-03-14)

6.删除以上各题创建的所有除法器。

drop trigger trg1,trg2,trg3,trg4,trg5

【实验小结】

通过对存储过程和触发器的创建，我认识到自己在触发器这块还有点欠缺。对触发器的语法结构不太了解，以后要加强联系。