关于使用mysql存储过程和触发器的结合

存储过程

应用场景

无传参，并返回结果（这里列举的是关于id自增）

场景：在某个表有insert操作时，往activity_business_info表中新增一条数据，并将insert操作中的id赋给activity_business_info的business_id

1.因为表activity_business_info的id不是自增的，所以这里需要创建一个函数来生成id

CREATE DEFINER=`root`@`%` FUNCTION `gene_long_id`() RETURNS bigint(20)#定义返回类型
BEGIN
DECLARE new_id BIGINT;#定义一个new_id的变量
SELECT MAX(id)+1 into new_id from activity_business_info;#查询最大id并+1
if new_id is null THEN #空值的判断
set new_id = 1;
END if;
RETURN new_id; #返回结果
END

有传参，并且返回查询结果

场景：在数据插入之前，根据insert数据中的"project_id"，查到其所属的区域id（dept_id）和区域名称(dept_name)

CREATE DEFINER=`root`@`%` PROCEDURE `get_region_id`(IN `project_id` BIGINT(20),OUT region VARCHAR(255)) #in为传参变量和类型  out为返回变量和类型
BEGIN 
#这块写查询逻辑开始
select concat(dept_id,',',dept_name) from sys_dept ## 这里把区域id和区域名称做了一个拼接，不知道怎么返回两个值，所以就拼接一下 
where find_in_set(dept_id,
(select ancestors from sys_dept where pt_var_1 = `project_id` and del_flag = '0')
) 
and dept_type = 'region' 
and del_flag = '0';
#这块写查询逻辑结束
END

触发器（结合存储过程）

应用场景

场景1：在total_plan表新增之后再在activity_business_info表中插入一条数据，其中gene_long_id()是一个生成id的函数，在上面有展示；

create TRIGGER 触发器名称 after insert on total_plan
for each row
begin
insert into activity_business_info(id,business_id,business_table_name,task_status) values(gene_long_id(),NEW.id,'total_plan','5');
end;

场景2：在equipment_allot 表做insert操作之前，改变insert中的值

CREATE TRIGGER upd_region_allot BEFORE insert on equipment_allot 
for each row 
begin 
	declare newRegionId BIGINT(20); #定义变量
	declare newRegionName VARCHAR(255);
	select 
	#这里将查询到的结果赋值给定义的变量，注意只能有一个into
	dept_id,dept_name into newRegionId,newRegionName 
	from prj_to_region 
	where pt_var_1 = NEW.project_id limit 1;
	#这里就是将变量赋值给insert语句中了，NEW就是insert的对象
	set NEW.region_id = newRegionId;
	set NEW.region = newRegionName;
end;

场景3：在业务表执行删除操作的时候，根据业务表id 删除另一张表的数据（关联字段类型不一致，但是值一致）

CREATE TRIGGER del_act_demand AFTER DELETE ON total_demand FOR EACH ROW
BEGIN
#这里注意要用old，因为是删除的数据
delete from activity_business_info where business_id = (old.id); 
END;

一个具有注脚的文本。¹

---

1. 注脚的解释 ↩︎