任务描述
本关任务: 为表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) MySQL的流程控制编程(参见存储过程实训); (2) 触发器的基本知识; (3) 触发器的创建; (4) 触发触发器的时机; (5) 触发触发器的事件; (6) 触发器内的特殊表。
触发器
触发器是与某个表绑定的命名存储对象,与存储过程一样,它由一组语句组成,当这个表上发生某个操作(insert,delete,update)时,触发器被触发执行。触发器一般用于实现业务完整性规则。当primary key,foreigh key, check等约束都无法实现某个复杂的业务规则时,可以考虑用触发器来实现。
触发器的创建
创建触发器的语句: CREATE TRIGGER trigger_name
trigger_time trigger_event
ON tbl_name FOR EACH ROW
trigger_body
- trigger_nme: 每个触发器有一个唯一的命名
- trigger_time: 触发的时机,二选一: BEFORE | AFTER
- trigger_event: 触发事件,三选一: INSERT | UPDATE | DELETE
- tbl_name: 与触发器绑定的表
- trigger_body: 触发器程序体,可由变量定义、赋值,流程控制,SQL语句等组成。但触发器体内不能使用create,alter,drop等DDL语句,也不能使用start transaction, commit,rollback等事务相关语句。
与创建存储过程、函数一样,创建触发器时也要用delimiter语句重新指定触发器定义语句的界符(触发器内语句的分隔符仍为分号),在触发器定义之后,再把界符更改回去。
before与after触发器的区别:
- before触发器在试图激活触发器的那条语句(insert|delete|update)之前执行。
- after触发器仅在before触发器(如果有的话)和试图激活触发器的那条语句都成功执行后才执行。
- before触发器或after触发器如果未能成功执行,则激活触发器的语句也不会执行。
触发器内的特殊表
在触发器内可以使用两类特殊表:
- old表和new表.它总是与触发器绑定的表有相同的结构,且只能在触发器内访问。
- delete触发器可以访问old表,其内容为被delete掉的数据。
- insert触发器可以访问new表,其内容为insert的新数据。
- update触发器可以访问old表和new表,old表保存着修改前的数据,new表保存着修改后的内容。
编程要求
在右侧代码文件编辑器里补充代码,实现本任务所要求的完整性业务规则。当插入的数据不符合要求时,拒绝数据的插入,并反馈出错信息: (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 ;