【数据库——MySQL(实战项目1)】(4)图书借阅系统——触发器
作者:mmseoamin日期:2023-12-21

目录

  • 1. 简述
  • 2. 功能代码
    • 2.1 创建两个触发器,分别在借出或归还图书时,修改借阅人表中的已借数目(附加:借阅人表的总借书数、图书表的借阅次数以及更新图书表的图书状态为(已借出/在架上))字段;
    • 2.2 创建触发器,当借阅者已借阅的书籍数目达到限额(附加:当借阅者证件号不存在、借阅者星级为 `0`、重复借入同一本书籍【未还同一本书的情况下】、图书已经被他人借了(此情况只发生在线上预约的时候-目前构建的数据库只适用于线下)、书籍不存在时)时,禁止借入新的书籍;
    • 2.3 创建触发器,当要从借阅人表中删除借阅人时(毕业了),需要先判断此借阅人是否有书未还,有的话禁止从借阅人表中删除此借阅人。
    • 结尾

      1. 简述

      在介绍本项目功能前,突然有感而发,讲讲我是如何对待程序员不写注释的吧(不感兴趣的小伙伴可以跳过这段话~)。作为一名合格的程序员,写注释是最基本的要求!!!它有如下好处:

      1. 帮助你回忆起这段代码在干什么?大家都写过代码吧,代码长度或长或短。可能一些短代码在仔细阅读后可以知道它在干什么,但是阅读代码的时间我相信我已经看了几个短代码的注释了!所以写注释可以提高我们读代码的效率(这是基于短代码而言的)。对于长代码,比如我写过的背单词软件(森林背词),仅仅是完成基础的背词、复习、调整各单词复习时间、利用数据库存储数据(不包含一些活动、优惠券之类)的代码就有 1w+ 的代码行数了(python 代码,如果是其它的代码可能更长~),如果不写注释,加上过了一段时间之后,连我自己都不记得某一段代码是干什么的了。。。但是有注释就不一样了,想要了解或者修改某一个功能代码时,只要搜索以下注释的关键字就能马上定位(这个也是血的教训!!!)。在重构森林背词之前,我用 python 写过一次,那时候写的功能更简单,但是要想从旧版本升级到新版本(重构),就要对原代码有一定的了解,因为之前没写过注释,所以看每个功能都很慢,最后干脆从头重写代码,这也导致我的工作量激增~
      2. 写注释除了帮助你自己了解和修改代码外,也可以帮助别人了解和修改你的代码。你也不想接手一个旧项目的代码,发现里面啥注释也没有吧,这样当你要维护的时候,时间成本是很大的。
      3. 当然注释的质量也是很重要的,要是一段注释说了跟没说一样,或者说的牛头不对马尾,那只会浪费你的时间。
      4. 除了注释,还有一个重点就是功能一定要分模块来写,不要全写在一个文件里面,要不然要你修改的时候你会崩溃。这里还有一个好处就是可以多人协作编写不同的功能代码~

      那么下面就正式开始图书借阅系统的最后一篇介绍文章~


      这篇文章将主要完成图书借阅系统的 9~11 题(触发器),即:

      1. 创建两个触发器,分别在借出或归还图书时,修改借阅人表中的已借数目(附加:借阅人表的总借书数、图书表的借阅次数以及更新图书表的图书状态为(已借出/在架上))字段;
      2. 创建触发器,当借阅者已借阅的书籍数目达到限额(附加:当借阅者证件号不存在、借阅者星级为 0、重复借入同一本书籍【未还同一本书的情况下】、图书已经被他人借了(此情况只发生在线上预约的时候-目前构建的数据库只适用于线下)、书籍不存在时)时,禁止借入新的书籍;
      3. 创建触发器,当要从借阅人表中删除借阅人时(毕业了),需要先判断此借阅人是否有书未还,有的话禁止从借阅人表中删除此借阅人。

      注:临时多了一些想法,把第 10 题新增了一些功能~

      2. 功能代码

      2.1 创建两个触发器,分别在借出或归还图书时,修改借阅人表中的已借数目(附加:借阅人表的总借书数、图书表的借阅次数以及更新图书表的图书状态为(已借出/在架上))字段;

      # 借书触发器
      drop TRIGGER if exists tri_update_borrowerlend;
      delimiter $
      create TRIGGER tri_update_borrowerlend after insert on information for each row
      begin
      	update borrower
      		set borrowedCount = borrowedCount + 1
      		where id = new.borrowerid;
      	update borrower
      		set borrowedAll = borrowedAll + 1
      		where id = new.borrowerid;
      	update book
      		set lendCount = lendCount + 1
      		where id = new.bookid;
      	update book
      		set state = '已借出'
      		where id = new.bookid;
      end$
      delimiter ;
      
      # 还书触发器
      drop TRIGGER if exists tri_update_borrowerreturn;
      delimiter $
      create TRIGGER tri_update_borrowerreturn after update on information for each row
      begin
      	update borrower
      		set borrowedCount = borrowedCount - 1
      		where id = new.borrowerid;
      	update book
      		set state = '在架上'
      		where id = new.bookid;
      end$
      delimiter ;
      

      检测触发器是否正确:

      # (触发器1)在借出图书时,修改借阅人表中的已借数目(附加:借阅人表的总借书数、图书表的借阅次数以及更新图书表的图书状态为(已借出/在架上))字段;
      select * from borrower;
      select book.id as 图书编号, book.lendCount as 借阅次数, book.state as 图书状态 from book;
      call p_InsertLeadInfo(2020312011047,101101699412);
      select * from borrower;
      select book.id as 图书编号, book.lendCount as 借阅次数, book.state as 图书状态 from book;
      # (触发器2)在归还图书时,修改借阅人表中的已借数目字段;
      call p_UpdateLeadInfo(2020312011047,101101699412);
      select * from borrower;
      select book.id as 图书编号, book.lendCount as 借阅次数, book.state as 图书状态 from book;
      

      测试结果如下:

      1. 触发器1(借书触发器):

        【数据库——MySQL(实战项目1)】(4)图书借阅系统——触发器,在这里插入图片描述,第1张

        可见此触发器是生效的,能够在借出图书时,将借阅人表中的当前借阅数目 +1,借阅人表的总借书数 +1、图书表的借阅次数 +1 以及图书表的图书状态从“在架上”更新为“已借出”。

      2. 触发器2(还书触发器):

        【数据库——MySQL(实战项目1)】(4)图书借阅系统——触发器,在这里插入图片描述,第2张

        可见此触发器是生效的,能够在归还图书时,将借阅人表中的当前借阅数目 -1 以及图书表的图书状态从“已借出”更新为“在架上”。

      2.2 创建触发器,当借阅者已借阅的书籍数目达到限额(附加:当借阅者证件号不存在、借阅者星级为 0、重复借入同一本书籍【未还同一本书的情况下】、图书已经被他人借了(此情况只发生在线上预约的时候-目前构建的数据库只适用于线下)、书籍不存在时)时,禁止借入新的书籍;

      drop trigger if EXISTS tri_insertinfo;
      delimiter $
      create TRIGGER tri_insertinfo before insert on information for each row
      begin
      	# 借阅者已借阅的书籍数目达到限额可使用存储函数f_ReBorrowTimes()完成
      	declare a int;
      	declare b int;
      	declare repeat_borrow int;		# 是否重复借入书籍
      	declare c int;		# 0:图书已借出:1:图书在架上
      	declare d int;
      	select count(*) into a
      	from borrower
      	where borrower.id = new.borrowerid;
      	
      	select count(*) into b
      	from borrower
      	where borrower.grade = 0 and borrower.id = new.borrowerid;
      	
      	SELECT if(returnDateReality is null, 0, 1) INTO repeat_borrow
      	FROM information
      	WHERE information.borrowerid = new.borrowerid AND information.bookid = new.bookid
      	ORDER BY returnDateReality ASC
      	LIMIT 1;
      	
      	select if(book.state='已借出',0,1) into c
      	from book
      	where book.id = new.bookid;
      	
      	select count(*) into d
      	from book
      	where book.id = new.bookid;
      	
      	if a = 0 then 
      		SIGNAL SQLSTATE '12345' 
      		set message_text = '证件号不存在';
      	elseif b != 0 then 
      		SIGNAL SQLSTATE '12346' 
      		set message_text = '借阅人等级为0(已列为失信人,禁止借入书籍)';
      	elseif f_ReBorrowTimes(new.borrowerid) <= 0 then 
      		SIGNAL SQLSTATE '12347' 
      		set message_text = '已借阅的书籍数目达到限额';
      	elseif repeat_borrow = 0 then 
      		SIGNAL SQLSTATE '12348' 
      		set message_text = '书籍禁止重复借入';
      	elseif c = 0 then 
      		SIGNAL SQLSTATE '12349' 
      		set message_text = '图书已被他人借阅';
      	elseif d = 0 then 
      		SIGNAL SQLSTATE '12350' 
      		set message_text = '图书不存在';
      	end if;
      	
      end$
      delimiter ;
      

      检测触发器是否正确:

      # 借阅者已借阅的书籍数目达到限额
      call p_InsertLeadInfo(2020312011047,101102308670);
      call p_InsertLeadInfo(2020312011047,101102698766);
      call p_InsertLeadInfo(2020312011047,451100004441);
      # 借阅者证件号不存在
      call p_InsertLeadInfo(9999999999999,101101699410);
      # 借阅者星级为0
      update borrower set grade = 0 where id = 2018210210205;
      call p_InsertLeadInfo(2018210210205,101101699410);
      # 重复借入同一本书籍【未还同一本书的情况下】
      call p_InsertLeadInfo(1000000000001,101101699410);
      call p_InsertLeadInfo(1000000000001,101101699410);
      # 图书已经被他人借了
      call p_InsertLeadInfo(1000000000002,101101699410);
      # 书籍不存在
      call p_InsertLeadInfo(1000000000002,999999999999);
      

      测试结果如下:

      1. 已借阅书籍达到限额(学生)【教师借阅是超过 10 本书就会触发此触发器,这里不进行演示,感兴趣的读者可自行验证~】:

        【数据库——MySQL(实战项目1)】(4)图书借阅系统——触发器,在这里插入图片描述,第3张

        从上面 触发器2(还书触发器) 的借阅人表可知证件号为“ 2020312011047 ”的借阅人(学生)已经借了 3 本书了,所以再借 2 本后,当继续借多 1 本时,显示借阅书籍数目达到上限,无法继续借阅。

      2. 证件号不存在:

        【数据库——MySQL(实战项目1)】(4)图书借阅系统——触发器,在这里插入图片描述,第4张

        证件号 9999999999999 不存在时,无法借阅书籍。

      3. 借阅人星级为 0 (已列为失信人,禁止借入书籍):

        【数据库——MySQL(实战项目1)】(4)图书借阅系统——触发器,在这里插入图片描述,第5张

        失信人(即长期逾期不还图书的用户)无法借阅书籍。

        注:此系统没有写降低用户星级的代码(例如长期逾期不还图书的用户根据次数或者时长降低用户星级),同样可以按照用户恢复正常使用后多少天就增加用户星级(增加的星级不能超过 5 颗星,默认用户最多只有 5 颗星),读者可以自行编写~

      4. 书籍禁止重复借入:

        【数据库——MySQL(实战项目1)】(4)图书借阅系统——触发器,在这里插入图片描述,第6张

        书籍在第一次成功借出,在第二次借出失败。即书籍禁止重复借入(未还的情况下再次借入,即借入行为重复操作)。

      5. 图书已被他人借阅:

        【数据库——MySQL(实战项目1)】(4)图书借阅系统——触发器,在这里插入图片描述,第7张

        这本书刚刚已经借出了(在测试 4. 书籍禁止重复借入 时借出了),因此他人不可再借阅,其实在实际当中,这种情况基本上不会发生(除非用户本人一直借这本书 或者 这本书遗失被其他人借阅)。

      6. 图书不存在:

        【数据库——MySQL(实战项目1)】(4)图书借阅系统——触发器,在这里插入图片描述,第8张

        当输入的图书编号(如这里的 999999999999)不存在时,无法借入,其实这种情况也不会发生,除非是借阅时工作人员输入错误 或者 机器识别错误。

      2.3 创建触发器,当要从借阅人表中删除借阅人时(毕业了),需要先判断此借阅人是否有书未还,有的话禁止从借阅人表中删除此借阅人。

      drop trigger if EXISTS tri_delete_borrower;
      delimiter $
      create TRIGGER tri_delete_borrower before delete on borrower for each row
      begin
      	declare a int DEFAULT 0;
      	select count(*) into a
      	from book join information on book.id = information.bookid
      						join borrower on borrower.id = information.borrowerid
      	where borrower.id = old.id and information.returnDateReality is null;
      	if a != 0 then 
      		SIGNAL SQLSTATE '12351' 
      		set message_text = '借阅人还有书籍未还,无法删除此借阅人';
      	end if;
      	
      	# 如若数据库储存量过大,不想保存非在校师生数据,可以保留下述删除语句
      	delete from information where information.borrowerid = old.id;
      	
      end$
      delimiter ;
      

      检测触发器是否正确:

      # 借阅人有书籍未还,无法删除
      delete from borrower where id = 2020312011047;
      # 借阅人无书籍未还,可以删除
      select * from borrower;
      delete from borrower where id = 2021312011002;
      select * from borrower;
      

      测试结果如下:

      1. 借阅人有书籍未还,无法删除:

        【数据库——MySQL(实战项目1)】(4)图书借阅系统——触发器,在这里插入图片描述,第9张

      2. 借阅人没有书籍未还,可以删除:

        【数据库——MySQL(实战项目1)】(4)图书借阅系统——触发器,在这里插入图片描述,第10张

      结尾

      好啦,本次的 MySQL 实战项目1 的介绍就到此结束了,后续如若有其它好的实战项目,我会继续分享~

      上一篇文章:【数据库——MySQL(实战项目1)】(3)图书借阅系统——存储函数