mysql 事件

/*开启event_scheduler*/
set global event_scheduler =1;  


/*创建存储过程*/
DROP PROCEDURE IF EXISTS transDataProcedure;
CREATE PROCEDURE transDataProcedure()
BEGIN
/**
*1.复制临时表所有更新的数据到目标表；
*2.根据临时表的被删除标记，更新案件表对应案件不可用；
*3.对被复制的临时表数据更新为不可用
**/
REPLACE INTO cathy_rh.tbl_case_scanc_temp() SELECT * FROM cathyjcy.tbl_case_scanc_temp ct where ct.enabled_flag = 1;
UPDATE cathy_rh.tbl_case_scanction c SET c.enabled_flag = 0 where c.TEMP_CASE_ID IN(SELECT ct.ID FROM cathyjcy.tbl_case_scanc_temp ct where ct.enabled_flag = 1 and ct.DELETE_FLAG = 1);
UPDATE cathyjcy.tbl_case_scanc_temp ct SET ct.enabled_flag = 0 where ct.enabled_flag = 1;
END;


/**
*创建EVENT_TRANS_DATA任务
*每隔1小时执行一次transDataProcedure()过程
*/
create event if not exists EVENT_TRANS_DATA
on schedule every 1 HOUR  
on completion preserve  
do call transDataProcedure();  


/*开户事件任务*/
alter event EVENT_TRANS_DATA ON COMPLETION PRESERVE ENABLE;