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

1、图书馆管理系统数据库-创建数据库和数据表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,ma

2、xsize=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 i

3、nt 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,-读者

4、年龄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

5、 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)-创建“分类号”为

6、外部键)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 ('

7、男','女'),-性别只能在“男”和“女”之间选择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

8、 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 b

9、total=BTotalNum,bborrowed=BborrowedNum from insertedif(btotal<bborrowed)beginrollback transactionprint '借阅失败！'print'对不起，此书已经没有库存，无法进行本次借书操作！'endgouse LibrarySystemgo-借书要求（读者最多借阅量）-假定教师最多只能借十本,本科生最多只能借五本书，研究生最多可以借八本书-create trigger tri_RBorrowNumon ReaderBookfor insertasdeclare no

10、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'对不起，你的借阅总量已经达到本，无法进行本次借书操作！请归还部分书籍后，再进行下次

11、借书操作！' endendselect 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'对不起，你的借阅总量已经达到本，无法进行本次借书操作！请归还部分书籍后，再进行下次借书操作！' endendsele

12、ct 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'对不起，你的借阅总量已经达到本，无法进行本次借书操作！请归还部分书籍后，再进行下次借书操作！' endend go-续借次数要求-假定教师最多允许续借四次，本

13、科生最多允许续借两次，研究生最多可以续借三次-use LibrarySystemgoCreate trigger tri_RRenewBookon ReaderBookfor updateasdeclare t tinyintselect t=inserted.RReborrowTimes from inserted,Reader where Reader.Readertype='教师'begin if(t>4)-教师最多允许续借四次 begin rollback transaction print '续借失败！' print '对不起，你的续借次

14、数已经达到了四次，已经无法再续借！' endendselect t=inserted.RReborrowTimes from inserted,Reader where Reader.Readertype='本科生'begin if(t>2)-本科生最多允许续借两次 begin rollback transaction print '续借失败！' print '对不起，你的续借次数已经达到了两次，已经无法再续借！' endendselect t=inserted.RReborrowTimes from inserted,Reader

15、 where Reader.Readertype='研究生'begin if(t>3)-研究生最多允许续借三次 begin rollback transaction print '续借失败！' print '对不起，你的续借次数已经达到了三次，已经无法再续借！' endendgouse LibrarySystemgo-读者还书信息入ReturnBook表-Create trigger tri_RReturnBookon ReaderBookfor deleteasdeclare t int,tno varchar(10),Bid varch

16、ar(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(t>0)-t>0说明该读者过去借过同一本书begin set t=t+1endelsebegin set t=1end-向Re

17、turnBook表中插入信息-第一部分（主码先入）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

18、 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 varcha

19、r(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,RBorrowDa

20、te)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,GETDAT

21、E()+' 日'+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)-判断读者是否借阅

22、此书，如果没有借阅此书，则不能进行还书操作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 Re

23、ader 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()+'

24、分'+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

25、 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

26、 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)+' 分'+daten

27、ame(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-以读者编号

28、为表名创建临时性数据表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,

29、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(