实训8-触发器 - 数据库系统原理实践MySQL - computer-science | Mi Manchi = yuan = Whatever is worth doing at all is worth doing well

# 为投资表 property 实现业务约束规则 - 根据投资类别分别引用不同表的主码

# 任务描述

本关任务：
为表 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 的业务规则 (一经销售的理财、保险和基金产品信息会永久保存，不会被删除或修改，即使不再销售该类产品)。

# 相关知识

为了完成本关任务，你需要掌握：

MySQL 的流程控制编程 (参见存储过程实训)；
触发器的基本知识；
触发器的创建；
触发触发器的时机；
触发触发器的事件；
触发器内的特殊表。

# 触发器

触发器是与某个表绑定的命名存储对象，与存储过程一样，它由一组语句组成，当这个表上发生某个操作 (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 表保存着修改后的内容。

# 编程要求

在右侧代码文件编辑器里补充代码，实现本任务所要求的完整性业务规则。当插入的数据不符合要求时，拒绝数据的插入，并反馈出错信息：

pro_type 数据不合法时，显示:
type x is illegal!
这里，x 系指试图插入的 pro_type 值。
pro_type = 1, 但 pro_pif_id 不是 finances_product 表中的某个主码值，显示:
finances product #x not found!
这里，x 系指试图插入的 pro_pif_di 的值。
pro_type = 2, 但 pro_pif_id 不是 insurance 表中的某个主码值，显示:
insurance #x not found!
这里，x 系指试图插入的 pro_pif_id 的值。
pro_type = 3, 但 pro_pif_id 不是 fund 表中的某个主码值，显示:
fund #x not found!
这里，x 系指试图插入的 pro_pif_id 的值。

提示：

查阅 MySQL 的字符串函数，构造出错信息；
当数据不合法时，用 signal sqlstate 语句抛出异常，并设置出错信息：

SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg;

其中，通用 SQLSTATE '45000' 意指用户定义的待处理异常，msg 需替换成你想要显示的提示信息 (不超过 128 个字符)。

	use finance1;
	drop trigger if exists before_property_inserted;
	-- 请在适当的地方补充代码，完成任务要求：
	delimiter $$
	CREATE TRIGGER before_property_inserted BEFORE INSERT ON property
	FOR EACH ROW
	BEGIN
	declare tp int default new.pro_type;
	declare id int default new.pro_pif_id;
	declare msg varchar(50);
	if tp = 1 then
	if id not in (select p_id from finances_product) then
	set msg = concat("finances product #", id, " not found!");
	end if;
	elseif tp = 2 then
	if id not in (select i_id from insurance) then
	set msg = concat("insurance #", id, " not found!");
	end if;
	elseif tp = 3 then
	if id not in (select f_id from fund) then
	set msg = concat("fund #", id, " not found!");
	end if;
	else
	set msg = concat("type ", tp, " is illegal!");
	end if;
	if msg is not null then
	signal sqlstate "45000" set message_text = msg;
	end if;

	END$$

	delimiter ;

在上面的代码中， new 应该是指即将插入的这一行数据

数据库