图书管理系统触发器和存储过程.doc

图书馆管理系统数据库-创建数据库和数据表create database LibrarySystemon primary(name=LibrarySystem,filename=f:LibrarySystemLibrarySystem.mdf,size=10240KB,maxsize=unlimited,filegrowth=1024KB)log on(name=LibrarySystem_log,filename=f:LibrarySystemLibrarySystem_log.ldf,size=1024KB,maxsize=unlimited,filegrowth=1024KB)go/*创建数据表：图书信息表，读者信息表，用户表，读者借阅表，读者还书表等*/use LibrarySystemgo-图书信息表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)-创建“分类号”为主键) gouse LibrarySystemgo-读者信息表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 nvarchar(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 LibrarySystemgo-借书要求（书本没有库存，则无法进行借书操作）-create trigger tri_Bookon Bookfor updateasdeclare btotal int,bborrowed intselect btotal=BTotalNum,bborrowed=BborrowedNum from insertedif(btotal10)-假定教师最多只能借十本书 begin rollback transaction print 借阅失败！ print对不起，你的借阅总量已经达到本，无法进行本次借书操作！请归还部分书籍后，再进行下次借书操作！ endendselect no=inserted.Rno from inserted,Reader where Reader.Readertype=本科生begin select num=count(*) from ReaderBook where Rno=no if(num5)-假定本科生最多只能借五本书 begin rollback transaction Print借阅失败! print对不起，你的借阅总量已经达到本，无法进行本次借书操作！请归还部分书籍后，再进行下次借书操作！ endendselect no=inserted.Rno from inserted,Reader where Reader.Readertype=研究生begin select num=count(*) from ReaderBook where Rno=no if(num8)-假定研究生最多只能借八本书 begin rollback transaction Print借阅失败! print对不起，你的借阅总量已经达到本，无法进行本次借书操作！请归还部分书籍后，再进行下次借书操作！ endend go-续借次数要求-假定教师最多允许续借四次，本科生最多允许续借两次，研究生最多可以续借三次-use LibrarySystemgoCreate trigger tri_RRenewBookon ReaderBookfor updateasdeclare t tinyintselect t=inserted.RReborrowTimes from inserted,Reader where Reader.Readertype=教师begin if(t4)-教师最多允许续借四次 begin rollback transaction print 续借失败！ print 对不起，你的续借次数已经达到了四次，已经无法再续借！ endendselect t=inserted.RReborrowTimes from inserted,Reader where Reader.Readertype=本科生begin if(t2)-本科生最多允许续借两次 begin rollback transaction print 续借失败！ print 对不起，你的续借次数已经达到了两次，已经无法再续借！ endendselect t=inserted.RReborrowTimes from inserted,Reader where Reader.Readertype=研究生begin if(t3)-研究生最多允许续借三次 begin rollback transaction print 续借失败！ print 对不起，你的续借次数已经达到了三次，已经无法再续借！ endendgouse LibrarySystemgo-读者还书信息入ReturnBook表-Create trigger tri_RReturnBookon ReaderBookfor deleteasdeclare t int,tno varchar(10),Bid varchar(30)select top 1 tno=Rno from deletedselect top 1 Bid=Bid from deleted-图书信息更改过程update Bookset BborrowedNum=BborrowedNum-1where Bid=Bid-判断RrturnBook表中该读者是否已借过同样一本书籍select t=RborrowTimes from ReturnBook where Rno=tno and Bid=Bidif(t0)-t0说明该读者过去借过同一本书begin set t=t+1endelsebegin set t=1end-向ReturnBook表中插入信息-第一部分（主码先入）insertinto ReturnBook(Rno,Bid,RborrowTimes,ReturnDate)values (tno,Bid,t,getdate()-第二部分（读者、书本信息）update ReturnBookset 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=BidGo图书馆管理系统数据库-存储过程的创建-读者借阅图书存储过程-use LibrarySystemgocreate procedure RBorrowBookno varchar(10),Bid varchar(30)asdeclare bname varchar(50)-判断读者是否已经借阅同一本书，如果已有借阅记录，则不能进行借书操作if exists(select * from ReaderBook where Rno=no and Bid=Bid)beginprint对不起，你已经借阅了同一本图书，故而无法进行此次借书操作，请核实！endelsebegin-图书信息更改update Bookset BborrowedNum=BborrowedNum+1where Bid=Bid-读者借阅过程insertinto ReaderBook (Rno,Bid,RBorrowDate)values (no,Bid,getdate()-输出借书成功信息declare rname varchar(8)select rname=Rname from Reader where Rno=noselect bname=Bname from Book where Bid=Bidselect 编号为+no+，姓名为+rname+的读者，于+datename(year,GETDATE()+ 年+datename(month,GETDATE()+ 月+datename(day,GETDATE()+ 日+datename(hour,GETDATE()+ 时+datename(minute,GETDATE()+ 分+datename(second,GETDATE()+ 秒，成功地从图书馆借出+bname+一书！ as 读者借书成功信息endgo-读者还书存储过程-use LibrarySystemgocreate procedure ReturnBookno varchar(10),Bid varchar(30)asdeclare bname varchar(50)-判断读者是否借阅此书，如果没有借阅此书，则不能进行还书操作if not exists(select * from ReaderBook where Rno=no and Bid=Bid)beginprint对不起，你没有借阅此书，故而无法进行此次还书操作，请核实！endelsebegin-读者还书过程deletefrom ReaderBookwhere Rno=no and Bid=Bid-#图书信息更改过程在触发器tri_RReturnBook中#-输出还书成功信息declare rname varchar(8)select rname=Rname from Reader where Rno=noselect bname=Bname from Book where Bid=Bidselect 编号为+no+，姓名为+rname+的读者，于+datename(year,GETDATE()+ 年+datename(month,GETDATE()+ 月+datename(day,GETDATE()+ 日+datename(hour,GETDATE()+ 时+datename(minute,GETDATE()+ 分+datename(second,GETDATE()+ 秒，成功地向图书馆归还+bname+一书！ as 读者还书成功信息endgo-读者续借图书存储过程-use LibrarySystemgocreate procedure RRenewBookno varchar(10),Bid varchar(30)asdeclare bname varchar(50),renewNum char(1),time smalldatetime-判断读者是否借阅此书，如果没有借阅此书，则不能进行续借操作if not exists(select * from ReaderBook where Rno=no and Bid=Bid)beginprint对不起，你没有借阅此书，故而无法进行此次续借操作，请核实！endelsebegin-读者续借图书过程update ReaderBookset RReborrowTimes=RReborrowTimes+1,renewNum=RReborrowTimes+1,time=RBorrowDate=getdate()-续借一次后，重新计入时间where Rno=no and Bid=Bid-输出续借成功信息declare rname varchar(8)select rname=Rname from Reader where Rno=noselect bname=Bname from Book where Bid=Bidselect 编号为+no+，姓名为+rname+的读者，于+datename(year,time)+ 年+datename(month,time)+ 月+datename(day,time)+ 日+datename(hour,time)+ 时+datename(minute,time)+ 分+datename(second,GETDATE()+ 秒，成功地续借+bname+一书，续借次数共为+renewNum+ 次！ as 读者续借成功信息endgo-读者查询借阅图书情况存储过程-use LibrarySystemgocreate procedure RQueryBookno varchar(10)asdeclare 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)-向临时表中插入数据-第一部分（插入子查询）insertinto RtempTable(书名,图书类别,借书日期,/*最后续借日期,*/续借次数)select Bname,Btype,RBorrowDate/*,RRecentlyRenew*/,RReborrowTimesfrom Book,ReaderBookwhere ReaderBook.Rno=no and Book.Bid=ReaderBook.Bid-第二部分declare rname char(8)select rname=Rname from Reader where Rno=noupdate RtempTable set 读者编号=no,姓名=rname-第三部分（计算最后续借到超期还剩天数，并插入对应列）select total=count(*),i=1 from RtempTable-获得RtempTable表中的记录总数while (i=total)beginselect top 1 time=最后续借日期,bname=书名from RtempTable where 书名not in (select top (i-1) 书名from RtempTable)set day=30-datediff(day,time,getdate()-读者一次借阅可阅读图书达30 天-判断最后一次续借到现在是否超期if(day0)begin update RtempTable set 是否超期=是 where 书名=bnameende