数据库第三次大作业(全答案)

数据库第三次大作业(全答

案)

-标准化文件发布号：（9456-EUATWK-MWUB-WUNN-INNUL-DDQTY-KII

4．作业3——存储过程与触发器设计任务

任务：

1）为BookStore数据库增加一个用户oper1，允许oper1对图书表和借还表进行修改操作。

use BookStore

go

exec sp_addlogin operl,'（此处为密码）','BookStore'

go

exec sp_addrolemember'db_datareader','operl'

grant update on图书表to operl

grant update on借还表to operl

2）设计存储过程

a)数据初始化过程sp_Init。包含的操作有：所有的用于测试图书信息、

教师信息、历史记录清空，输入图书分类、输入管理员信息、输入借书

规定。

略，界面进行输入或者代码输入作业用数据

b)某教师在某时间借了某本书，由某管理员办理。

create procedure proc_lendbook

@aid char(10),

@atime datetime,

@ISBN char(20),

@aname nchar(4)

as

begin

update图书表

set状态='借出'

where ISBN=@ISBN

insert into借还表(ISBN,教师工号,借办代理人,借出日期)

values(@ISBN,@aid,@aname,@atime)

end

c)某教师在某时间还了某本书，由某管理员办理

create procedure proc_returnbook

@bid char(10),

@btime datetime,

@ISBN char(20),

@bname nchar(4)

as

begin

update图书表

set状态='库存'

where ISBN=@ISBN

update借还表

set还办代理人=@bname,归还日期=@btime

where教师工号=@bid and ISBN=@ISBN

end

d)查看某人的全部借还书记录

create procedure proc_getReaderlog

@cname n char(4),

@cid char(10)

as

begin

select b.姓名,c.书名,a.借出日期,归还日期

from借还表 a join教师表 b on a.教师工号=b.教师工号join图书表 c on

a.ISBN=c.ISBN

where b.姓名=@cname or b.教师工号=@cid

end

e)查看某本书的历史流转记录

create procedure proc_getBooklog

@bookname nchar(10),

@ISBN char(20)

as

begin

select教师表.姓名,图书表.书名,借还表.借出日期,归还日期

from借还表 a join教师表 b on a.教师工号=b.教师工号join图书表 c on

a.ISBN=c.ISBN

where a.ISBN=@ISBN or c.书名=@bookname

end

f)某管理员在某时间给全部借阅过期者发催还邮件。

部分有错误的需要将修改催还业务编号为自增长（identity（1,1））,还有整个表都要允许空值：

例：alter table 催还表

alter column 办代理人 char(4) null

create procedure proc_writeEmailLog

@dname char(4),

@dtime datetime

as

begin

insert into催还表(教师工号)

select教师工号

from借还表a join图书表 b on a.ISBN=b.ISBN join类型表 c on b.类型名=c.类型

名

where datediff(day, a.借出日期,getdate())>c.借阅期限and a.归还日期is null

update催还表

set办代理人=@dname,催还日期=@dtime

where催还日期is null

end

g)对书名进行模糊搜索（搜索条件可以是多个模糊列值的组合，例如：搜索书名包含“网络”两个字，并且是近3年出版的图书）

create procedure proc_search

@bookname nchar(10),

@max tinyint

as

begin

select*

from图书表

where datediff(year,出版时间,getdate())<@max and 书名like'%'+@bookname+'%' end

3）设计触发器

a)当oper1用户对图书状态进行修改时，自动在借还表追加相应的数据。

create trigger tri_a

on图书表after update

as

declare @status char(4)

declare @ISBN char(20)

select @status=状态from inserted

select @ISBN=ISBN from inserted

if user_name()='operl'

begin

if @status='借出'

insert into借还表(ISBN,借办代理人,借出日期)

values(@ISBN,user_name(),getdate())

else if @status='库存'

update借还表

set还办代理人=user_name(),归还日期=getdate()

where ISBN=@ISBN

end

b)不允许oper1用户对催还表信息进行删除操作，如果删除进行回滚。

create trigger tri_b

on催还表after delete

as