4、数据库课程设计《SQL Server图书馆管理系统》之创建触发器源码

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 ------------借书要求（读者最多借阅量）----------- --------假定教师最多只能借十本 create trigger tri_TBorrowNum on TeacherBook for insert as declare @no char(10),@num tinyint --------获得教师编号 select @no=Tno from inserted --------统计教师借书总量并做相应处理 select @num=count(*) from TeacherBook where Tno=@no if(@num>10)--------假定教师最多只能借十本书 begin rollback transaction print '借阅失败！' print'对不起，你的借阅总量已经达到10本，无法进行本次借书操作！请归还部分书籍后，再进行下次借书操作！' end go --------假定学生最多只能借五本书 create trigger tri_SBorrowNum on StudentBook for insert as declare @no char(10),@num tinyint --------获得学生学号 select @no=Sno from inserted --------统计学生借书总量并做相应处理 select @num=count(*) from StudentBook where Sno=@no if(@num>5)--------假定学生最多只能借四本书 begin rollback transaction print '借阅失败！' print'对不起，你的借阅总量已经达到5本，无法进行本次借书操作！请归还部分书籍后，再进行下次借书操作！' end go -------------续借次数要求--------- --------假定教师最多允许续借四次 Create trigger tri_TRenewBook on TeacherBook for update as declare @t tinyint select @t=TReborrowTimes from inserted if(@t>4)--------教职工最多允许续借四次 begin rollback transaction print '续借失败！' print '对不起，你的续借次数已经达到了四次，已经无法再续借！' end go --------假定学生最多允许续借三次 Create trigger tri_SRenewBook on StudentBook for update as declare @s tinyint select @s=SReborrowTimes from inserted if(@s>3)--------学生最多允许续借三次 begin rollback transaction print '续借失败！' print '对不起，你的续借次数已经达到了三次，已经无法再续借！' end go -----------读者还书信息入RDeleted表----------- --------教师还书信息入RDeleted表 Create trigger tri_TReturnBook on TeacherBook for delete as declare @t tinyint,@tno varchar(10),@classifyNo varchar(30) select top 1 @tno=Tno from deleted select top 1 @classifyNo=ClassifyNo from deleted -----------图书信息更改过程 update Book set BborrowedNum=BborrowedNum-1 where ClassifyNo=@classifyNo --------判断RDeleted表中该读者是否已借过同样一本书籍 select @t=RborrowTimes from RDeleted where Rno=@tno and ClassifyNo=@classifyNo if(@t>0)----@t>0说明该读者过去借过同一本书 begin set @t=@t+1 end else begin set @t=1 end ---------向RDeleted表中插入信息 -----第一部分（主码先入） insert into RDeleted(Rno,ClassifyNo,RborrowTimes,ReturnDate) values (@tno,@classifyNo,@t,getdate()) -----第二部分（读者、书本信息） update RDeleted set Rname=(select Tname from Teacher where Tno=@tno) ,Rsex=(select Tsex from Teacher where Tno=@tno) ,Rage=(select Tage from Teacher where Tno=@tno) ,Bname=(select Bname from Book where ClassifyNo=@classifyNo) ,Bwriter=(select Bwriter from Book where ClassifyNo=@classifyNo) ,Btype=(select Btype from Book where ClassifyNo=@classifyNo) ,BpubAdr=(select BpubAdr from Book where ClassifyNo=@classifyNo) ,Bprice=(select Bprice from Book where ClassifyNo=@classifyNo) where Rno=@tno and ClassifyNo=@classifyNo go --------学生还书信息入RDeleted表 Create trigger tri_SReturnBook on StudentBook for delete as declare @s tinyint,@sno varchar(10),@classifyNo varchar(30) select top 1 @sno=Sno from deleted select top 1 @classifyNo=ClassifyNo from deleted -----------图书信息更改过程 update Book set BborrowedNum=BborrowedNum-1 where ClassifyNo=@classifyNo --------判断RDeleted表中该读者是否已借过同样一本书籍 select @s=RborrowTimes from RDeleted where Rno=@sno and ClassifyNo=@classifyNo if(@s>0)----@t>0说明该读者过去借过同一本书 begin set @s=@s+1 end else begin set @s=1 end ---------向RDeleted表中插入信息 -----第一部分（主码先入） insert into RDeleted(Rno,ClassifyNo,RborrowTimes,ReturnDate) values (@sno,@classifyNo,@s,getdate()) -----第二部分（读者、书本信息） update RDeleted set Rname=(select Sname from Student where Sno=@sno) ,Rsex=(select Ssex from Student where Sno=@sno) ,Rage=(select Sage from Student where Sno=@sno) ,Bname=(select Bname from Book where ClassifyNo=@classifyNo) ,Bwriter=(select Bwriter from Book where ClassifyNo=@classifyNo) ,Btype=(select Btype from Book where ClassifyNo=@classifyNo) ,BpubAdr=(select BpubAdr from Book where ClassifyNo=@classifyNo) ,Bprice=(select Bprice from Book where ClassifyNo=@classifyNo) where Rno=@sno and ClassifyNo=@classifyNo go