图书管理系统触发器和存储过程
图书馆管理系统数据库---------------创建数据库和数据表 create database LibrarySystem
on primary
(
name='LibrarySystem',
filename='f:\LibrarySystem\LibrarySystem.mdf',
size=10240KB,
maxsize=unlimited,
filegrowth=1024KB
)
log on
(
name='LibrarySystem_log',
filename='f:\LibrarySystem\LibrarySystem_log.ldf',
size=1024KB,
maxsize=unlimited,
filegrowth=1024KB
)
go
/*创建数据表：图书信息表，读者信息表，用户表，读者借阅表，读者还书表等*/
use LibrarySystem
go
----------图书信息表
CREATE TABLE Book
(
Bid nchar(30) NOT NULL,----分类号
Bname varchar(50) NOT NULL,----书名
Bwriter nvarchar(20) NOT NULL,----作者
BpubAdr varchar(30) NOT NULL,----出版社
BpubDate smalldatetime,----出版日期
Btype varchar(20),----类别
BtotalNum int NOT NULL,----总量
BborrowedNum int default 0,----借出量默认值为零
constraint Book_PK primary key (Bid)----创建“分类号”为主键
)
go
use LibrarySystem
go
----------读者信息表 CREATE TABLE Reader
(
Rno nvarchar(10) NOT NULL,----读者编号
Rname nvarchar(20) NOT NULL,----姓名
Rsex char(2) check(Rsex in ('男','女')),----性别只能在“男”和“女”之间选择
Rage int not null,---读者年龄
Rphone nvarchar(15),----电话/手机
Readertype nvarchar(20) check (Readertype in ('本科生','研究生','教师')),----读者类型只能
在“本科生”，“研究生”，“教师”之间选择
constraint Reader_PK primary key (Rno)----创建“读者编号”为主键
)
go
----------读者借阅信息表
create table ReaderBook
(
Rno nvarchar(10) NOT NULL,----读者编号
Bid nchar(30) NOT NULL,----分类号
RBorrowDate smalldatetime NOT NULL,----借阅日期
RBackDate smalldatetime not null,----应还书日期
RReborrowTimes tinyint default 0,----续借次数默认为次
RoverdueDays int,----图书超期天数
constraint ReaderBook_PK primary key (Rno,Bid),----创建“读者编号，分类号”为主键
foreign key (Rno) references Reader(Rno),----创建“读者编号”为外部键
foreign key (Bid) references Book(Bid)----创建“分类号”为外部键
)
go
------用户表
create table Admin
(
AdminId varchar(10)not null,----用户ID
AdminPsw varchar(10)not null,----用户密码
AdminName varchar(20)not null,----用户名
constraint Admin_PK primary key(AdminId)----创建用户ID为主键
)
Create table RReturnBook
(
Rno nvarchar(10) NOT NULL,
Rname nvarchar(20) NOT NULL,----姓名
Rsex char(2) check(Rsex in ('男','女')),----性别只能在“男”和“女”之间选择
Rage int not null,---读者年龄
Bid nchar(30) NOT NULL,----分类号
Bname varchar(50) NOT NULL,----书名 Bwriter nvarcha
r(20),---作者
Btype varchar(20),---图书类别
BpubAdr varchar(30),---出版社
RborrowTimes tinyint default 1,---记录读者借借阅同一本书的次数
ReturnDate smalldatetime,---还书时间
constraint RReturnBook_PK primary key (Rno,Bid,RborrowTimes),---创建主键
constraint Rno_Fk foreign key (Rno) references Reader(Rno),
constraint RBid_FK foreign key (Bid) references Book(Bid)---创建外码
)
Go 图书馆管理系统数据库
--------------触发器的创建 use LibrarySystem
go
------------借书要求（书本没有库存，则无法进行借书操作）-----------
create trigger tri_Book
on Book
for update
as
declare @btotal int,@bborrowed int
select @btotal=BTotalNum,@bborrowed=BborrowedNum from inserted
if(@btotal<@bborrowed)
begin
rollback transaction
print '借阅失败！'
print'对不起，此书已经没有库存，无法进行本次借书操作！'
end
go
use LibrarySystem
go
------------借书要求（读者最多借阅量）-----------
----假定教师最多只能借十本,本科生最多只能借五本书，研究生最多可以借八本书----
create trigger tri_RBorrowNum
on ReaderBook
for insert
as
declare @no char(10),@num int
--------获得教师编号
select @no=inserted.Rno from inserted,Reader where Reader.Readertype='教师'
--------统计教师借书总量并做相应处理
begin
select @num=count(*)
from ReaderBook where Rno=@no
if(@num>10)--------假定教师最多只能借十本书
begin
rollback transaction
print '借阅失败！'
print'对不起，你的借阅总量已经达到本，无法进行本次借书操作！请归还部分
书籍后，再进行下次借书操作！'
end
end
select @no=inserted.Rno from inserted,Reader where Reader.Readertype='本科生'
begin
select @num=count(*)
from ReaderBook
where Rno=@no
if(@num>5)---------假定本科生最多只能借五本书
begin
rollback transaction
Print'借阅失败!'
print'对不起，你的借阅总量已经达到本，无法进行本次借书操作！请归还部分
书籍后，再进行下次借书操作！'
end
end
select @no=inserted.Rno from inserted,Reader where Reader.Readertype='研究生'
begin
select @num=count(*)
from ReaderBook
where Rno=@no
if(@num>8)---------假定研究生最多只能借八本书
begin
rollback transaction
Print'借阅失败!'
print'对不起，你的借阅总量已经达到本，无法进行本次借书操作！请归还部分
书籍后，再进行下次借书操作！'
end
end
go
-------------续借次数要求---------
--------假定教师最多允许续借四次，本科
生最多允许续借两次，研究生最多可以续借三次
------------
use LibrarySystem go
Create trigger tri_RRenewBook
on ReaderBook
for update
as
declare @t tinyint
select @t=inserted.RReborrowTimes from inserted,Reader where Reader.Readertype='教师'
begin
if(@t>4)--------教师最多允许续借四次
begin
rollback transaction
print '续借失败！'
print '对不起，你的续借次数已经达到了四次，已经无法再续借！'
end
end
select @t=inserted.RReborrowTimes from inserted,Reader where Reader.Readertype='本科生'
begin
if(@t>2)--------本科生最多允许续借两次
begin
rollback transaction
print '续借失败！'
print '对不起，你的续借次数已经达到了两次，已经无法再续借！'
end
end
select @t=inserted.RReborrowTimes from inserted,Reader where Reader.Readertype='研究生'
begin
if(@t>3)--------研究生最多允许续借三次
begin
rollback transaction
print '续借失败！'
print '对不起，你的续借次数已经达到了三次，已经无法再续借！'
end
end
go
use LibrarySystem
go
-----------读者还书信息入ReturnBook表-----------
Create trigger tri_RReturnBook
on ReaderBook for delete
as
declare @t int,@tno varchar(10),@Bid varchar(30)
select top 1 @tno=Rno from deleted
select top 1 @Bid=Bid from deleted
-----------图书信息更改过程
update Book
set BborrowedNum=BborrowedNum-1
where Bid=@Bid
--------判断RrturnBook表中该读者是否已借过同样一本书籍
select @t=RborrowTimes from ReturnBook where Rno=@tno and Bid=@Bid
if(@t>0)----@t>0说明该读者过去借过同一本书
begin
set @t=@t+1
end
else
begin
set @t=1
end
---------向ReturnBook表中插入信息
-----第一部分（主码先入）
insert
into ReturnBook(Rno,Bid,RborrowTimes,ReturnDate)
values (@tno,@Bid,@t,getdate())
-----第二部分（读者、书本信息）
update ReturnBook
set
Rname=(select Rname from Reader where Rno=@tno)
,Rsex=(select Rsex from Reader where Rno=@tno)
,Rage=(select Rage from Reader where Rno=@tno)
,Bname=(select Bname from Book where Bid=@Bid)
,Bwriter=(select Bwriter from Book where Bid=@Bid)
,Btype=(select Btype from Book where Bid=@Bid)
,BpubAdr=(select BpubAdr from Book where Bid=@Bid)
where Rno=@tno and Bid=@Bid
Go 图书馆管理系统数据库
-------------存储过程的创建 --------------------------------------读者借阅图书存储过程-------------------------------
use LibrarySystem
go
create procedure RBorrowBook
@no varchar(10),@Bid varchar(30)
as declare @bname varchar(50)
-----------判断
读者是否已经借阅同一本书，如果已有借阅记录，则不能进行借书操作
if exists(select * from ReaderBook where Rno=@no and Bid=@Bid)
begin
print'对不起，你已经借阅了同一本图书，故而无法进行此次借书操作，请核实！'
end
else
begin
-----------图书信息更改
update Book
set BborrowedNum=BborrowedNum+1
where Bid=@Bid
-----------读者借阅过程
insert
into ReaderBook (Rno,Bid,RBorrowDate)
values (@no,@Bid,getdate())
-----------输出借书成功信息
declare @rname varchar(8)
select @rname=Rname from Reader where Rno=@no
select @bname=Bname from Book where Bid=@Bid
select '编号为'+@no+'，姓名为'+@rname+'的读者，于'+datename(year,GETDATE())+' 年
'+datename(month,GETDATE())+' 月'+datename(day,GETDATE())+' 日
'+datename(hour,GETDATE())+' 时'+datename(minute,GETDATE())+' 分
'+datename(second,GETDATE())+' 秒，成功地从图书馆借出《'+@bname+'》一书！' as 读者
借书成功信息
end
go
--------------------------------------读者还书存储过程-------------------------------
use LibrarySystem
go
create procedure ReturnBook
@no varchar(10),@Bid varchar(30)
as
declare @bname varchar(50)
-----------判断读者是否借阅此书，如果没有借阅此书，则不能进行还书操作
if not exists(select * from ReaderBook where Rno=@no and Bid=@Bid)
begin
print'对不起，你没有借阅此书，故而无法进行此次还书操作，请核实！'
end
else
begin
-----------读者还书过程 delete
from ReaderBook
where Rno=@no and Bid=@Bid
--############"图书信息更改过程在触发器tri_RReturnBook中"###########
-----------输出还书成功信息
declare @rname varchar(8)
select @rname=Rname from Reader where Rno=@no
select @bname=Bname from Book where Bid=@Bid
select '编号为'+@no+'，姓名为'+@rname+'的读者，于'+datename(year,GETDATE())+' 年
'+datename(month,GETDATE())+' 月'+datename(day,GETDATE())+' 日
'+datename(hour,GETDATE())+' 时'+datename(minute,GETDATE())+' 分
'+datename(second,GETDATE())+' 秒，成功地向图书馆归还《'+@bname+'》一书！' as 读者
还书成功信息
end
go
--------------------------------------读者续借图书存储过程-------------------------------
use LibrarySystem
go
create procedure RRenewBook
@no varchar(10),@Bid varchar(30)
as
declare @bname varchar(50),@renewNum char(1),@time smalldatetime
-----------判断读者是否借阅此书，如果没有借阅此书，则不能进行续借操作
if not exists(select * from ReaderBook where Rno=@no and Bid=@Bid)
begin
print'对不起，你没有借阅此书，故而无法进行此次续借操作，请核实！'
end
else
begin
-----------读者续借图书过程
update ReaderBook
set
RReborrowTimes=RReborrow
Times+1,@renewNum=RReborrowTimes+1,@time=RBorrowDate
=getdate()----续借一次后，重新计入时间
where Rno=@no and Bid=@Bid
-----------输出续借成功信息
declare @rname varchar(8)
select @rname=Rname from Reader where Rno=@no
select @bname=Bname from Book where Bid=@Bid
select '编号为'+@no+'，姓名为'+@rname+'的读者，于'+datename(year,@time)+' 年
'+datename(month,@time)+' 月'+datename(day,@time)+' 日'+datename(hour,@time)+' 时'+datename(minute,@time)+' 分'+datename(second,GETDATE())+' 秒，成功地续借
《'+@bname+'》一书，续借次数共为'+@renewNum+' 次！' as 读者续借成功信息
end
go
--------------------------------------读者查询借阅图书情况存储过程-------------------------------
use LibrarySystem
go
create procedure RQueryBook
@no varchar(10)
as
declare @bname varchar(50), @i int,@total int,@time smalldatetime,@day int
---if (charindex('T',@no)>0)
---begin
-----------以读者编号为表名创建临时性数据表
declare @RtempTable table
(
读者编号nvarchar(10),
姓名nvarchar(8),
书名varchar(50) primary key,
图书类别Varchar(20),
借书日期smalldatetime not null,
---最后续借日期smalldatetime not null,
本次借阅还剩天数int,
续借次数tinyint not null,
是否超期nvarchar(2)
)
-------------向临时表中插入数据
-------第一部分（插入子查询）
insert
into @RtempTable(书名,图书类别,借书日期,/*最后续借日期,*/续借次数)
select Bname,Btype,RBorrowDate/*,RRecentlyRenew*/,RReborrowTimes
from Book,ReaderBook
where ReaderBook.Rno=@no and Book.Bid=ReaderBook.Bid
-------第二部分
declare @rname char(8)
select @rname=Rname from Reader where Rno=@no
update @RtempTable set 读者编号=@no,姓名=@rname
-------第三部分（计算最后续借到超期还剩天数，并插入对应列） select @total=count(*),@i=1 from @RtempTable---获得@RtempTable表中的记录总数
while (@i<=@total)
begin
select top 1 @time=最后续借日期,@bname=书名
from @RtempTable
where 书名not in
(
select top (@i-1) 书名
from @RtempTable
)
set @day=30-datediff(day,@time,getdate())-----读者一次借阅可阅读图书达30 天
----------判断最后一次续借到现在是否超期
if(@day<0)begin
update @RtempTable set 是否超期='是' where 书名=@bname
end
else
begin
update @RtempTable set 是否超期='否' where 书名=@bname
end
update @RtempTable set 本次借阅还剩天数=@day where 书名=@bname
set @i=@i+1
end
-------------显示借阅图书情况的信息：姓名、书名、图书类别、借书日期、最后续借日
期、续借次数
select * from @RtempTable
go
--------------------------------------读者检索的图书信息
存储过程-------------------------------
use LibrarySystem
go
create procedure RIndexBook
@parm nvarchar(50)
as
declare @Bid nvarchar(30),@bname nvarchar(50)
set @bname='%'+@parm+'%'
declare @i bigint,@total bigint
declare @tempIndex table
(
图书分类号varchar(30), 书名varchar(50),
出版社nvarchar(30),
作者nvarchar(10),
出版日期smalldatetime,
图书总量int,
图书余量int
)
select @total=count(*),@i=1 from Book
while(@i<=@total)
begin
---------从Book 表中抽取第@i 行记录进行分析
select top 1 @Bid=Bid from Book where Bid not in(select top (@i-1) Bid from Book)
if(exists (select * from Book where Bname like @bname and Bid=@Bid))
begin
insert into @tempIndex
select Bid,Bname,BpubAdr,Bwriter,BpubDate,BtotalNum,BtotalNum-BborrowedNum
from Book where Bid=@Bid
end
set @i=@i+1
end
select '你所检索的图书信息如下：'
select * from @tempIndex
go