触发器、存储过程（视图）、事件定时执行

触发器、存储过程（视图）、事件定时执行

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();    -- 回调 存储过程