• 【数据库——MySQL（实战项目1）】（4）图书借阅系统——触发器

    目录

    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 ;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    # 还书触发器
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
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13

    检测触发器是否正确：

    # (触发器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
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11

    测试结果如下：

    1. 触发器1（借书触发器）：

      在这里插入图片描述

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

    2. 触发器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 ;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55

    检测触发器是否正确：

    # 借阅者已借阅的书籍数目达到限额
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
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16

    测试结果如下：

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

      在这里插入图片描述
      从上面 触发器2（还书触发器） 的借阅人表可知证件号为“ 2020312011047 ”的借阅人（学生）已经借了 3 本书了，所以再借 2 本后，当继续借多 1 本时，显示借阅书籍数目达到上限，无法继续借阅。

    2. 证件号不存在：

      在这里插入图片描述
      证件号 9999999999999 不存在时，无法借阅书籍。

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

      在这里插入图片描述
      失信人（即长期逾期不还图书的用户）无法借阅书籍。

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

    4. 书籍禁止重复借入：

      在这里插入图片描述
      书籍在第一次成功借出，在第二次借出失败。即书籍禁止重复借入（未还的情况下再次借入，即借入行为重复操作）。

    5. 图书已被他人借阅：

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

    6. 图书不存在：

      在这里插入图片描述
      当输入的图书编号（如这里的 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 ;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21

    检测触发器是否正确：

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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    测试结果如下：

    1. 借阅人有书籍未还，无法删除：

      在这里插入图片描述

    2. 借阅人没有书籍未还，可以删除：

      在这里插入图片描述

    结尾

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

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

  • 相关阅读:
    OpenCv——鼠标事件笔记
    高频SQL 判断三角形
    自己java复习笔记
    消息队列基本原理和选型对比
    Robust Real-time LiDAR-inertial Initialization（实时鲁棒的LiDAR惯性初始化）论文学习
    RedisTemplate乱码问题
    [BJDCTF2020]ZJCTF，不过如此 preg_replace /e模式漏洞
    11. v-if、v-show、v-html 的原理和区别？
    云通信接口更新迭代——SUBMAIL API V4正式上线
    mysql实战操作总结
  • 原文地址：https://blog.csdn.net/senlin_6688/article/details/133812388