为投资表property实现业务约束规则-根据投资类别分别引用不同表的主码【头哥题目】

一步步757

已于 2023-05-13 16:10:32 修改

阅读量579

点赞数 2

分类专栏：头歌例题文章标签：数据库 sql mysql

于 2023-05-07 23:59:55 首次发布

本文链接：https://blog.csdn.net/weixin_63226758/article/details/130550011

版权

头歌例题专栏收录该内容

4 篇文章 3 订阅

订阅专栏

任务描述

本关任务：为表property(资产表)编写一个触发器，以实现以下完整性业务规则：

如果pro_type = 1, 则pro_pif_id只能引用finances_product表的p_id；
如果pro_type = 2, 则pro_pif_id只能引用insurance表的i_id；
如果pro_type = 3, 则pro_pif_id只能引用fund表的f_id；
pro_type不接受(1,2,3)以外的值。

各投资品种一经销售，不会再改变；也不需考虑finances_product，insurance，fund的业务规则(一经销售的理财、保险和基金产品信息会永久保存，不会被删除或修改，即使不再销售该类产品)。

编程要求

在右侧代码文件编辑器里补充代码，实现本任务所要求的完整性业务规则。当插入的数据不符合要求时，拒绝数据的插入，并反馈出错信息： (1) pro_type数据不合法时，显示: type x is illegal! 这里，x系指试图插入的pro_type值。 (2) pro_type = 1,但pro_pif_id不是finances_product表中的某个主码值，显示: finances product #x not found! 这里,x系指试图插入的pro_pif_di的值。 (3) pro_type = 2,但pro_pif_id不是insurance表中的某个主码值，显示: insurance #x not found! 这里,x系指试图插入的pro_pif_id的值。 (3) pro_type = 3,但pro_pif_id不是fund表中的某个主码值，显示: fund #x not found! 这里,x系指试图插入的pro_pif_id的值。

提示： (1) 查阅MySQL的字符串函数，构造出错信息； (2) 当数据不合法时，用signal sqlstate 语句抛出异常，并设置出错信息： SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg; 其中，通用SQLSTATE '45000'意指用户定义的待处理异常，msg需替换成你想要显示的提示信息(不超过128个字符)。

测试说明

评测程序将执行你的语句创建触发器，然后对相关表执行insert操作，来触发你定义的触发器，然后查看预期的结果有没有发生。如果执行insert语句后，发生的结果与预期结果一致即过关。

use finance1;
drop trigger if exists before_property_inserted;
-- 请在适当的地方补充代码，完成任务要求：
delimiter $$
CREATE TRIGGER before_property_inserted BEFORE INSERT ON property
FOR EACH ROW 
BEGIN
IF (NEW.pro_type NOT IN (1,2,3)) THEN
    SET @msg = CONCAT('type ', NEW.pro_type, ' is illegal!');
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = @msg;
  END IF;
  IF (NEW.pro_type = 1) AND (SELECT COUNT(*) FROM finances_product WHERE p_id = NEW.pro_pif_id) = 0 THEN
    SET @msg = CONCAT('finances product #', NEW.pro_pif_id, ' not found!');
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = @msg;
  END IF;
  IF (NEW.pro_type = 2) AND (SELECT COUNT(*) FROM insurance WHERE i_id = NEW.pro_pif_id) = 0 THEN
    SET @msg = CONCAT('insurance #', NEW.pro_pif_id, ' not found!');
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = @msg;
  END IF;
  IF (NEW.pro_type = 3) AND (SELECT COUNT(*) FROM fund WHERE f_id = NEW.pro_pif_id) = 0 THEN
    SET @msg = CONCAT('fund #', NEW.pro_pif_id, ' not found!');
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = @msg;
  END IF;
END$$
 
delimiter ;

下面是对上述编程语句的解释

对“IF (NEW.pro_type NOT IN (1,2,3)) THEN”中的NEW进行说明

mysql触发器new old是一对，一起进行讲解

当使用insert语句的时候，对于插入数据后表来说新插入的那条数据就是new
当使用delete，update语句的时候，删除的那一条数据相对于删除数据后表的数据来说就是old

对"SET @msg = CONCAT('type ', NEW.pro_type, ' is illegal!');“中的concat进行说明

concat表示将多个字符串连接成一个字符串，在该题中将type，NEW.pro_type的值，is illegal! 这三个部分连成一个字符串。

对” SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = @msg;“中的SIGNAL SQLSTATE '45000'和 MESSAGE_TEXT进行说明

"signal sqlstate ‘45000’ "是 MySQL数据库中的一条sql语，用于抛出一个自定义的异常，这个异常的 SQLSTATE 值为 45000，表示用户自定义异常。当执行到这条语句时，会立即抛出异常并终止当前的 SQL 执行。

sqlstate是一个5个字符的sql状态码，用于指示错误信息。message_text是一个可选的错误信息，用于提供更加详细的错误信息。也就是说当输入了错误信息，并且使用了message_text会返回自定义的错误信息。

参考了@剪影的-头歌平台MySQL-触发器仅供参考文章

一步步757

关注

2
点赞
踩
5

收藏

觉得还不错? 一键收藏
0
评论
为投资表property实现业务约束规则-根据投资类别分别引用不同表的主码【头哥题目】

当插入的数据不符合要求时，拒绝数据的插入，并反馈出错信息： (1) pro_type数据不合法时，显示: type x is illegal!这里，x系指试图插入的pro_type值。触发器是与某个表绑定的命名存储对象，与存储过程一样，它由一组语句组成，当这个表上发生某个操作(insert,delete,update)时，触发器被触发执行。与创建存储过程、函数一样，创建触发器时也要用delimiter语句重新指定触发器定义语句的界符(触发器内语句的分隔符仍为分号)，在触发器定义之后，再把界符更改回去。
复制链接

扫一扫