数据库实验报告5

1.使用系统存储过程(sp_rename)将视图“V_SPJ”更名为“V_SPJ_三建”。（5分）

exec sp_rename v_spj, v_spj_三建;

2.针对SPJ数据库，创建并执行如下的存储过程：（共计35分）

(1)创建一个带参数的存储过程—jsearch。该存储过程的作用是：当任意输入一个工

程代号时，将返回供应该工程零件的供应商的名称(SNAME)和零件的名称

(PNAME)以及工程的名称(JNAME)。执行jsearch存储过程，查询“J1”对应的信息。（10分）

create proc jsearch @jno char(2)

as select sname, pname, jname from s,p,j,spj

where s.sno=spj.sno and p.pno=spj.pno and j.jno=spj.jno and spj.jno=@jno;

执行：exec jsearch 'J1'

(2)使用S表，为其创建一个加密的存储过程—jmsearch。该存储过程的作用是：当执

行该存储过程时，将返回北京供应商的所有信息。（10分）

创建加密存储过程：

create proc jmsearch with encryption as

select * from s where s.city='北京';

sp_helptext jmsearch;

(3)使用系统存储过程sp_helptext查看jsearch, jmsearch的文本信息。（5分）

用系统存储过程sp_helptext查看jsearch：

exec sp_help jsearch;

exec sp_helptext jsearch;

用系统存储过程sp_helptext查看jmsearch：exec sp_help jmsearch;

exec sp_helptext jmsearch;

(4)执行jmsearch存储过程，查看北京供应商的情况。（5分）exec jmsearch;

(5)删除jmsearch存储过程。（5分）

drop proc jmsearch;

3.针对Student数据库，创建和执行如下的触发器：（共计40分）

(1)删除SC表上的外键约束，针对SC表创建一个名为insert_s的INSERT触发器。

该触发器的功能：当用户向SC表中插入记录时，如果插入的cno值不是C表中Cno的已有值，则提示用户“不能插入记录这样的纪录”，否则提示“记录插入成功”。触发器创建成功之后，向SC表插入记录，验证触发器是否正常工作。(5分)

create trigger insert_s

on SC

instead of insert

as

declare @cno char(4)

begin

select @cno = cno from inserted

if @cno=any(select cno from C)

begin

select '记录插入成功'

insert into SC

select * from inserted

end

else

select'不能插入记录这样的记录'

end

return

验证：

insert into SC

values ('95007', '5', '99');

(2)为S表创建一个名为dele_s1的DELETE触发器，该触发器的作用是禁止删除S

表中的记录。触发器创建成功之后，删除S表中的记录，验证触发器是否正常工作。(5分)

create trigger dele_s1

on S

instead of delete

as

begin

rollback

print '禁止删除S表中的记录'

end

验证：

delete

from S

where sno = '95007'

(3)为S表创建一个名为dele_s2的DELETE触发器，该触发器的作用是删除S表中

的记录时删除SC表中该学生的选课纪录。触发器创建成功之后，删除S表中的记录删除S表中的记录，验证触发器是否正常工作(SC表中的数据被正常删除)。

(5分)

create trigger dele_s2 on S

after delete

as

declare @sno nchar(9)

begin

select @sno=sno from deleted

delete SC

where sno=@sno

end

删除验证：

delete S

where sno='95007'

(4)为S表创建一个名为update_s的UPDATE触发器，该触发器的作用是禁止更新S

表中“sdept”字段的内容。触发器创建成功之后，更新S表中“sdept”字段的内容，验证触发器是否正常工作。(5分)

create trigger update_s on S

after update

as

begin

if UPDATE(sdept)

begin

ROLLBACK

PRINT '禁止更新sdept字段'

End

END

验证：

update S

set sdept=’cf’

where sno='95001'

无法删除。

(5)禁用update_s触发器。禁用之后，更新S表中的“sdept”字段的内容，验证触发

器是否还继续正常工作。(5分)

disable trigger update_s on Sdisable trigger update_s on S

验证：

update S

set sdept='cf'

where sno='95001'