记录项目中用的触发器

最新推荐文章于 2022-05-19 16:18:12 发布

yzy85

最新推荐文章于 2022-05-19 16:18:12 发布

阅读量265

点赞数

本文链接：https://blog.csdn.net/yzy85/article/details/82999220

版权

1、从collecter中触发插入另一个库

begin 

declare icount int;

 INSERT INTO SYNC_TASK4 (TIPTOP_CDATE,TIPTOP_TABLE_NAME,TIPTOP_PRIKEY_VALUE2,TIPTOP_PRIKEY_OLDVALUE2,TIPTOP_PRIKEY_VALUE1,TIPTOP_PRIKEY_OLDVALUE1,TIPTOP_ACTION_TAG,TIPTOP_STATUS_TAG) values(now(),'em_sampledata_collect',NEW.ID,NEW.ID,NEW.ID,NEW.ID,'INSERT',0); 
 
	

	  INSERT INTO atm_ems.es_collect_data (collectItemCode,collectTime,collectValue,couValue) values(new.TagID, new.SampTime,new.		 TagValue,new.CouValue); 
		
		select count(1) into icount from atm_ems.es_collect_data_real where collectItemCode = new.TagID;
		if icount=0 then
				insert into atm_ems.es_collect_data_real (collectItemCode,collectTime,collectValue,couValue) values (new.TagID, new.SampTime,new.TagValue,new.TagValue);
		else
				UPDATE atm_ems.es_collect_data_real set collectTime=new.SampTime, collectValue=new.TagValue, couValue=new.TagValue where collectItemCode = new.TagID;
    end if;

 end

2、数据插入触发汇总天月年

begin
		declare insertTime datetime;
		declare beginTime datetime;
		declare endTime datetime; 
		declare sumValue decimal(16,4);
    declare icount int;
		set insertTime=date_add(new.collectTime, interval -1 minute);
		set beginTime=STR_TO_DATE(date_format(insertTime, '%Y-%m-%d %H:%00:%00'),'%Y-%m-%d %H:%i:%s');
		set endTime=STR_TO_DATE(date_format(date_add(insertTime, interval 1 hour), '%Y-%m-%d %H:%00:%00'),'%Y-%m-%d %H:%i:%s');
		
		
		
		select round(sum(couValue),4)  into sumValue from es_collect_data where collectItemCode = new.collectItemCode and collectTime>beginTime and collectTime<=endTime; 
    select count(1) into icount from es_collect_data_hour where collectItemCode = new.collectItemCode and collectTime=beginTime;
		if icount=0 then
				insert into es_collect_data_hour (collectItemCode,collectTime,couValue) values (new.collectItemCode,beginTime,sumValue);
		else
				UPDATE es_collect_data_hour set couValue=sumValue where collectItemCode = new.collectItemCode and collectTime=beginTime;
    end if;

		set beginTime=STR_TO_DATE(date_format(insertTime, '%Y-%m-%d %00:%00:%00'),'%Y-%m-%d %H:%i:%s');
		set endTime=STR_TO_DATE(date_format(date_add(insertTime, interval 1 day), '%Y-%m-%d %00:%00:%00'),'%Y-%m-%d %H:%i:%s');
		select round(sum(couValue),4)  into sumValue from es_collect_data where collectItemCode = new.collectItemCode and collectTime>beginTime and collectTime<=endTime; 
    select count(1) into icount from es_collect_data_day where collectItemCode = new.collectItemCode and collectTime=beginTime;
		if icount=0 then
				insert into es_collect_data_day (collectItemCode,collectTime,couValue) values (new.collectItemCode,beginTime,sumValue);
		else
				UPDATE es_collect_data_day set couValue=sumValue where collectItemCode = new.collectItemCode and collectTime=beginTime;
    end if;

		set beginTime=STR_TO_DATE(date_format(insertTime, '%Y-%m-%01 %00:%00:%00'),'%Y-%m-%d %H:%i:%s');
		set endTime=STR_TO_DATE(date_format(date_add(insertTime, interval 1 MONTH), '%Y-%m-%01 %00:%00:%00'),'%Y-%m-%d %H:%i:%s');
		select round(sum(couValue),4)  into sumValue from es_collect_data where collectItemCode = new.collectItemCode and collectTime>beginTime and collectTime<=endTime; 
    select count(1) into icount from es_collect_data_month where collectItemCode = new.collectItemCode and collectTime=beginTime;
		if icount=0 then
				insert into es_collect_data_month (collectItemCode,collectTime,couValue) values (new.collectItemCode,beginTime,sumValue);
		else
				UPDATE es_collect_data_month set couValue=sumValue where collectItemCode = new.collectItemCode and collectTime=beginTime;
    end if;
 end