触发器、存储过程(视图)、事件定时执行
1.触发器
1.1 before update
描述:更新触发前,补充行字段内容
触发条件:before update
触发表:performance_sys_project_score
delimiter //
CREATE TRIGGER `update_sys_project_score_order` BEFORE update ON `performance_sys_project_score`
FOR EACH ROW
begin
declare tmp_order_name varchar(255);
set tmp_order_name = (select project_name from performance_sys_project_info where project_id = OLD.project_id);
set NEW.order_id =
(case when OLD.order_id is null then
CONCAT('GD',new.project_id,substr(curdate(),1,4),substr(curdate(),6,2),substr(curdate(),9,2))
else OLD.order_id end
);
set NEW.order_name =
(case when OLD.order_name is null then
CONCAT(tmp_order_name,'工单',substr(curdate(),3,2),substr(curdate(),6,2),substr(curdate(),9,2))
else OLD.order_name end
);
end; //
delimiter ;
1.2 after update
描述:更新触发后,插入目标表行内容
触发条件:after update
触发表:performance_sys_project_score
目标插入表:performance_sys_project_order
delimiter //
CREATE TRIGGER `update_insert_sys_project_order` BEFORE update ON `performance_sys_project_score`
FOR EACH ROW
begin
declare tmp_project_name varchar(255);
declare tmp_rule_list varchar(255);
set tmp_project_name =
(select project_name from performance_sys_project_info where project_id = new.project_id);
set tmp_rule_list = new.rule_list;
if not exists (select * from performance_sys_project_order where order_id = new.order_id limit 1)
then
insert into performance_sys_project_order
(order_id,order_name,project_id,project_name,active,create_user,create_time,order_stu)
select new.order_id,new.order_name,new.project_id,
tmp_project_name as project_name, '1' active,
new.update_user as create_user,
new.update_time as create_time,
(case when tmp_rule_list is not null then 1 else 0 end) as order_stu
from performance_sys_project_score
where 1 = 1
and project_id = new.project_id
and new.rule_list is not null
group by project_id;
end if ;
end; //
delimiter ;
描述:更新触发后,插入目标表行内容,且避免重复插入,进行存在判断
触发条件:after update
触发表:performance_sys_project_score
目标插入表:performance_sys_project_warnning
delimiter //
CREATE TRIGGER `update_insert_sys_project_order` BEFORE update ON `performance_sys_project_score`
FOR EACH ROW
begin
declare tmp_project_name varchar(255);
declare tmp_rule_no3 varchar(255);
declare tmp_rule_no2 varchar(255);
declare tmp_rule_no1 varchar(255);
declare tmp_qsly varchar(255);
declare tmp_zzclxq varchar(255);
declare tmp_pfxz varchar(255);
set tmp_project_name = (select project_name from performance_sys_project_info where project_id = new.project_id);
set tmp_rule_no3 = (select rule_no3 from performance_sys_project_rule where id = new.rule_id);
set tmp_rule_no2 = (select rule_no2 from performance_sys_project_rule where id = new.rule_id);
set tmp_rule_no1 = (select rule_no1 from performance_sys_project_rule where id = new.rule_id);
set tmp_qsly = (select qsly from performance_sys_project_rule where id = new.rule_id);
set tmp_zzclxq = (select zzclxq from performance_sys_project_rule where id = new.rule_id);
set tmp_pfxz = (select pfxz from performance_sys_project_rule where id = new.rule_id);
if not exists (select * from performance_sys_project_warnning where project_id = new.project_id and rule_id = new.rule_id and warnning_type = '得分空值预警'limit 1)
then
insert into performance_sys_project_warnning
(project_id,project_name,warnning,warnning_type,active,rule_no3,rule_no2,rule_no1,
qsly,szfz,score,zzclxq,pfxz,rule_id)
select
new.project_id,
tmp_project_name as project_name,
(case when new.score is null then '0' else '1' end) warnning,
'得分空值预警' warnning_type,
'1' active,
tmp_rule_no3 as rule_no3,
tmp_rule_no2 as rule_no2,
tmp_rule_no1 as rule_no1,
tmp_qsly,
new.szfz,
new.score,
tmp_zzclxq as zzclxq,
tmp_pfxz as pfxz,
new.rule_id
from performance_sys_project_score
where 1 = 1
and new.score is null
and new.rule_id is not null
and new.rule_list is not null
group by new.project_id;
end if;
end; //
delimiter ;
1.3 单号自增与参数内传
delimiter //
CREATE TRIGGER `update_test_project_score_order` BEFORE update ON `test_project_score`
FOR EACH ROW
begin
declare dt char(8);
declare od_id varchar(255);
declare od_name varchar(255);
declare num int;
declare number int;
declare new_od varchar(255);
declare new_odname varchar(255);
declare tmp_order_name varchar(255);
set tmp_order_name = (select project_name from test_project_performance where project_id = new.project_id);
set dt = date_format(curdate(), '%Y%m%d');
select max(order_id) into od_id
from test_project_score
where project_id = old.project_id;
if od_id = '' or od_id is null then
set new_od = concat('GD',new.project_id,dt,'0001');
else
set number = right(od_id,4) + 1;
set new_od = right(concat('0000',number),4);
set new_od = concat('GD',new.project_id,dt,new_od);
end if;
set NEW.order_id =
(case when OLD.order_id is null then new_od else OLD.order_id end);
select max(order_name) into od_name
from test_project_score
where project_id = old.project_id;
if od_name = '' or od_name is null then
set new_odname = concat(tmp_order_name,'工单',dt,'0001');
else
set num = right(od_name,4) + 1;
set new_odname = right(concat('0000',num),4);
set new_odname = concat(tmp_order_name,'工单',dt,new_odname);
end if;
set NEW.order_name =
(case when old.order_name is null then new_odname else OLD.order_name end);
end
2.存储过程(视图)
描述:
目标表:performance_sys_project_warnning
数据来源:视图
2.1存储过程
描述:新产生预警信息到表performance_sys_project_warnning
delimiter //
CREATE DEFINER=`easybuild`@`%` PROCEDURE `insert_sys_warnning_reform_out_time`()
BEGIN
insert into performance_sys_project_warnning (
project_id,project_name,project_type,warnning,warnning_type,active,
rule_id,rule_no1,rule_no2,rule_no3,qsly,zzclxq,pfxz,
szfz,score)
select * from v_sys_reform_time_out;
END; //
delimiter ;
2.2视图
描述:先左独有连接,去除已有信息,筛选出新信息
create or replace view v_sys_reform_time_out as
select tb.* from (
select
t1.project_id,
t2.project_name,
t2.project_type,
(case when date_add(t1.update_time,interval 1 day) < CURRENT_TIMESTAMP() then '0' else '1' end) warnning,
'整改不及时预警' warnning_type,
'1' active,
t3.id as rule_id,
t3.rule_no1,
t3.rule_no2,
t3.rule_no3,
t3.qsly,
t3.zzclxq,
t3.pfxz,
t1.szfz,
t1.score
from performance_sys_project_score t1
left join performance_sys_project_info t2
on t1.project_id = t2.project_id
left join performance_sys_project_rule t3
on t1.rule_id = t3.id
where 1 = 1
and t1.rule_list = '1'
and t1.order_id is not null
and t1.rule_id is not null
group by t1.project_id,t3.rule_no3
) tb
left join performance_sys_project_warnning t4
on tb.project_id = t4.project_id
and tb.rule_id = t4.rule_id
and tb.warnning_type = t4.warnning_type
where t4.project_id is null;
3.事件定时执行
3.1创建事件与定时执行
-- 创建一个定时事件,,每1h执行一次
drop event sys_warnning_reform_out_time;
create event if not exists sys_warnning_reform_out_time
-- on schedule every 12 HOUR STARTS NOW() -- 从现在开始, 每1h执行一次
on schedule EVERY 1 HOUR STARTS NOW()
on completion PRESERVE
do call insert_sys_warnning_reform_out_time(); -- 回调 存储过程