mysql存储过程和触发器常用sql

小七蒙恩

已于 2022-06-22 10:35:13 修改

阅读量250

点赞数

分类专栏：开发记录文章标签： mysql sql 数据库触发器存储过程

于 2022-06-15 09:52:16 首次发布

本文链接：https://blog.csdn.net/qq_38105536/article/details/125291242

版权

开发记录专栏收录该内容

23 篇文章 0 订阅

订阅专栏

mysql存储过程和触发器常用sql

#查看当前数据库中所有触发器
SHOW TRIGGERS

#查看当前数据库中某个触发器的定义
SHOW CREATE TRIGGER 触发器名

#删除触发器
DROP TRIGGER IF EXISTS 触发器名称;  

#查看当前数据库中所有存储过程
show procedure status where db='traffic_monitor';

#查看当前数据库中存储过程sql
show create procedure 存储过程名称;



#删除当前数据库的存储过程
drop procedure if exists 存储过程名称;
drop procedure if exists CalculateDensityByPass;
drop procedure if exists CalculateEvent;
drop procedure if exists CalculateStatsEvent;

#删除当前数据库的触发器
drop TRIGGER if exists  触发器名称;
drop TRIGGER if exists  density_after_insert;
drop TRIGGER if exists  event_after_insert;
drop TRIGGER if exists  event_before_update;
drop TRIGGER if exists  pass_after_insert;
drop TRIGGER if exists  realtime_before_insert;
drop TRIGGER if exists  realtime_before_update;

触发器和存储过程实例：

CREATE TABLE `data_pass` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `device_code` varchar(32) NOT NULL COMMENT '设备编码',
  `event_type` varchar(32) NOT NULL COMMENT '事件类型;tms.event.type',
  `event_code` varchar(32) NOT NULL COMMENT '事件代码；tms.event.code',
  `event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '事件时间',
  `vehicle_id` varchar(32) DEFAULT NULL COMMENT '车辆ID',
  `file_url` varchar(256) DEFAULT NULL COMMENT '图片文件url',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7822 DEFAULT CHARSET=utf8 COMMENT='过车表';

#创建触发器pass_after_insert关联表data_pass，在插入的每一行后执行
CREATE TRIGGER `pass_after_insert` AFTER INSERT ON `data_pass` FOR EACH ROW BEGIN
  # 取得设备的relation
  select relation into @_relation from base_device where code= new.device_code;
  SET @_time = DATE_FORMAT(new.event_time,'%H');
  SET @_day = DATE_FORMAT(new.event_time,'%Y-%m-%d');
  case @_time
        #根据索引值，存在就更新，不存在就插入初始值
      when '00' then
        INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_00,c_00_length,c_00_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1) 
				ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_00 = c_00 + 1,c_00_length = c_00_length+1,c_00_avg = c_00_length/c_00;
      when '01' then 
        INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_01,c_01_length,c_01_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1) 
				ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_01 = c_01 + 1,c_01_length = c_01_length+1,c_01_avg = c_01_length/c_01;
      when '02' then 
        INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_02,c_02_length,c_02_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1) 
				ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_02 = c_02 + 1,c_02_length = c_02_length+1,c_02_avg = c_02_length/c_02;
      when '03' then 
        INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_03,c_03_length,c_03_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1) 
				ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_03 = c_03 + 1,c_03_length = c_03_length+1,c_03_avg = c_03_length/c_03;
      when '04' then 
        INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_04,c_00_length,c_04_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1) 
				ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_04 = c_04 + 1,c_04_length = c_04_length+1,c_04_avg = c_04_length/c_04;
      when '05' then 
        INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_05,c_05_length,c_05_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1) 
				ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_05 = c_05 + 1,c_05_length = c_05_length+1,c_05_avg = c_05_length/c_05;
      when '06' then 
        INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_06,c_06_length,c_06_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1) 
				ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_06 = c_06 + 1,c_06_length = c_06_length+1,c_06_avg = c_06_length/c_06;
      when '07' then 
        INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_07,c_07_length,c_07_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1) 
				ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_07 = c_07 + 1,c_07_length = c_07_length+1,c_07_avg = c_07_length/c_07;
      when '08' then 
        INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_08,c_08_length,c_08_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1) 
				ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_08 = c_08 + 1,c_08_length = c_08_length+1,c_08_avg = c_08_length/c_08;
      when '09' then 
        INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_09,c_09_length,c_09_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1) 
				ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_09 = c_09 + 1,c_09_length = c_09_length+1,c_09_avg = c_09_length/c_09;
      when '10' then 
        INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_10,c_10_length,c_10_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1) 
				ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_10 = c_10 + 1,c_10_length = c_10_length+1,c_10_avg = c_10_length/c_10;
      when '11' then 
        INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_11,c_11_length,c_11_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1) 
				ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_11 = c_11 + 1,c_11_length = c_11_length+1,c_11_avg = c_11_length/c_11;
      when '12' then 
        INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_12,c_12_length,c_12_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1) 
				ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_12 = c_12 + 1,c_12_length = c_12_length+1,c_12_avg = c_12_length/c_12;
      when '13' then 
        INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_13,c_13_length,c_13_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1) 
				ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_13 = c_13 + 1,c_13_length = c_13_length+1,c_13_avg = c_13_length/c_13;
      when '14' then 
        INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_14,c_14_length,c_14_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1) 
				ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_14 = c_14 + 1,c_14_length = c_14_length+1,c_14_avg = c_14_length/c_14;
      when '15' then 
        INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_15,c_15_length,c_15_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1) 
				ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_15 = c_15 + 1,c_15_length = c_15_length+1,c_15_avg = c_15_length/c_15;
      when '16' then 
        INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_16,c_16_length,c_16_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1) 
				ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_16 = c_16 + 1,c_16_length = c_16_length+1,c_16_avg = c_16_length/c_16;
      when '17' then 
        INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_17,c_17_length,c_17_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1) 
				ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_17 = c_17 + 1,c_17_length = c_17_length+1,c_17_avg = c_17_length/c_17;
      when '18' then 
        INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_18,c_18_length,c_18_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1) 
				ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_18 = c_18 + 1,c_18_length = c_18_length+1,c_18_avg = c_18_length/c_18;
      when '19' then 
        INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_19,c_19_length,c_19_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1) 
				ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_19 = c_19 + 1,c_19_length = c_19_length+1,c_19_avg = c_19_length/c_19;
      when '20' then 
        INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_20,c_20_length,c_20_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1) 
				ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_20 = c_20 + 1,c_20_length = c_20_length+1,c_20_avg = c_20_length/c_20;
      when '21' then 
        INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_21,c_21_length,c_21_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1) 
				ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_21 = c_21 + 1,c_21_length = c_21_length+1,c_21_avg = c_21_length/c_21;
      when '22' then 
        INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_22,c_22_length,c_22_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1) 
				ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_22 = c_22 + 1,c_22_length = c_22_length+1,c_22_avg = c_22_length/c_22;
      when '23' then 
        INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_23,c_23_length,c_23_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1) 
				ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_23 = c_23 + 1,c_23_length = c_23_length+1,c_23_avg = c_23_length/c_23;
      
    End case;
    #如果是20903
    if @_relation = 20903 then
      # 取得过车设备的上级设备
      select id,code, type into @_fid,@_fdeviceCode,@_ftype from base_device where id = (select f_id from base_device where code = new.device_code);
      # 计算上级设备的密度,进入的减去出来的
      select sum(value) into @_inport from data_realtime where device_code in (select code from base_device where f_id=@_fid and relation = 20902) and event_code = '0';
      select sum(value) into @_outport from data_realtime where device_code in (select code from base_device where f_id=@_fid and relation = 20903) and event_code = '0';
      set @_fvalue = @_inport - @_outport;
	  #出来的为空或者密度大于0，就更新实时数据表
      if @_outport is null or @_fvalue > 0 then
        INSERT INTO data_realtime(device_code,event_code,event_time,relation,value,data_id) VALUES(new.device_code,new.event_code,new.event_time,@_relation ,1,new.id) 
          ON DUPLICATE KEY UPDATE event_time=new.event_time,value=value+1,data_id=new.id;
      end if;
	#如果不是20903，就直接更新时数据表
    else
        INSERT INTO data_realtime(device_code,event_code,event_time,relation,value,data_id) VALUES(new.device_code,new.event_code,new.event_time,@_relation ,1,new.id) 
          ON DUPLICATE KEY UPDATE event_time=new.event_time,value=value+1,data_id=new.id;
    end if;

  #调用存储过程
  #根据过车计算密度
    Call CalculateDensityByPass(new.device_code,new.event_time);
   # 计算上级设备的事件数
    Call CalculateStatsEvent(new.device_code,new.event_code,@_day);
END;







CREATE DEFINER=`root`@`localhost` PROCEDURE `CalculateDensityByPass`(IN `d_code` varchar(32),IN `e_time` timestamp)
    COMMENT '根据过车计算密度'
BEGIN
  # 取得过车设备的上级设备
  select id,code, type into @_fid,@_fdeviceCode,@_ftype from base_device where id = (select f_id from base_device where code = d_code);
  
	# 计算上级设备的密度
	select sum(value) into @_inport from data_realtime where device_code in (select code from base_device where f_id=@_fid and relation = 20902) and event_code = '0';
	select sum(value) into @_outport from data_realtime where device_code in (select code from base_device where f_id=@_fid and relation = 20903) and event_code = '0';
  IF @_inport is null THEN
    set @_fvalue = 0;
  ELSEIF @_outport is null THEN
    set @_fvalue = @_inport;
  ELSEIF @_inport<@_outport THEN
    set @_fvalue = 0;
  ELSE
    set @_fvalue = @_inport - @_outport;
  END IF;
  
	INSERT INTO data_realtime(device_code,event_code,event_time,relation,value) VALUES (@_fdeviceCode,'2',e_time,20901,@_fvalue)
	  ON DUPLICATE KEY UPDATE event_time=e_time,value=@_fvalue;
	INSERT INTO data_realtime(device_code,event_code,event_time,relation,value) VALUES (@_fdeviceCode,'0',e_time,20902,@_inport)
	  ON DUPLICATE KEY UPDATE event_time=e_time,value=@_inport;
	INSERT INTO data_realtime(device_code,event_code,event_time,relation,value) VALUES (@_fdeviceCode,'0',e_time,20903,@_outport)
	  ON DUPLICATE KEY UPDATE event_time=e_time,value=@_outport;
	
  # 计算上上级设备的密度
  repeat  #循环执行找上级
    # 取得上上级设备
    select id,code, type into @_fid,@_fdeviceCode,@_ftype from base_device where id = (select f_id from base_device where code = @_fdeviceCode);
    # 计算上上级设备密度
    select sum(value) into @_fvalue from data_realtime where device_code in (select code from base_device where f_id=@_fid) and event_code = '2';
    INSERT INTO data_realtime(device_code,event_code,event_time,relation,value) VALUES (@_fdeviceCode,'2',e_time,20901,@_fvalue)
      ON DUPLICATE KEY UPDATE event_time=e_time,value=@_fvalue;
    # 计算上上级设备入口车辆数
    select sum(value) into @_fvalue from data_realtime where device_code in (select code from base_device where f_id=@_fid) and relation = 20902;
    INSERT INTO data_realtime(device_code,event_code,event_time,relation,value) VALUES (@_fdeviceCode,'0',e_time,20902,@_fvalue)
      ON DUPLICATE KEY UPDATE event_time=e_time,value=@_fvalue;
    # 计算上上级设备出口车辆数
    select sum(value) into @_fvalue from data_realtime where device_code in (select code from base_device where f_id=@_fid) and relation = 20903;
    INSERT INTO data_realtime(device_code,event_code,event_time,relation,value) VALUES (@_fdeviceCode,'0',e_time,20903,@_fvalue)
      ON DUPLICATE KEY UPDATE event_time=e_time,value=@_fvalue;
 #直到@_ftype满足条件停止
 UNTIL @_ftype=20801
  End repeat;
END





CREATE DEFINER=`root`@`%` PROCEDURE `CalculateStatsEvent`(IN `d_code` varchar(32),IN `e_code` varchar(32),IN `e_time` timestamp)
BEGIN
  # 计算上级设备的事件数
  set @_fdeviceCode = d_code;
  select relation into @_relation FROM base_device where code = d_code;
  repeat  #循环执行找上级
    # 取得上级设备
    select id,code, type into @_fid,@_fdeviceCode,@_ftype from base_device where id = (select f_id from base_device where code = @_fdeviceCode);
    # 计算上级设备事件数
    select SUM(c_all), SUM(c_all_length), SUM(c_all_avg), SUM(c_00), SUM(c_00_length), SUM(c_00_avg), SUM(c_01), SUM(c_01_length), SUM(c_01_avg),
           SUM(c_02), SUM(c_02_length), SUM(c_02_avg), SUM(c_03), SUM(c_03_length), SUM(c_03_avg), SUM(c_04), SUM(c_04_length), SUM(c_04_avg),
           SUM(c_05), SUM(c_05_length), SUM(c_05_avg), SUM(c_06), SUM(c_06_length), SUM(c_06_avg), SUM(c_07), SUM(c_07_length), SUM(c_07_avg),
           SUM(c_08), SUM(c_08_length), SUM(c_08_avg), SUM(c_09), SUM(c_09_length), SUM(c_09_avg), SUM(c_10), SUM(c_10_length), SUM(c_10_avg),
           SUM(c_11), SUM(c_11_length), SUM(c_11_avg), SUM(c_12), SUM(c_12_length), SUM(c_12_avg), SUM(c_13), SUM(c_13_length), SUM(c_13_avg),
           SUM(c_14), SUM(c_14_length), SUM(c_14_avg), SUM(c_15), SUM(c_15_length), SUM(c_15_avg), SUM(c_16), SUM(c_16_length), SUM(c_16_avg),
		   SUM(c_17), SUM(c_17_length), SUM(c_17_avg), SUM(c_18), SUM(c_18_length), SUM(c_18_avg), SUM(c_19), SUM(c_19_length), SUM(c_19_avg),
		   SUM(c_20), SUM(c_20_length), SUM(c_20_avg), SUM(c_21), SUM(c_21_length), SUM(c_21_avg), SUM(c_22), SUM(c_22_length), SUM(c_22_avg),
		   SUM(c_23), SUM(c_23_length), SUM(c_23_avg)
	into @_c_all, @_c_all_length, @_c_all_avg, @_c_00, @_c_00_length, @_c_00_avg, @_c_01, @_c_01_length, @_c_01_avg, 
         @_c_02, @_c_02_length, @_c_02_avg, @_c_03, @_c_03_length, @_c_03_avg, @_c_04, @_c_04_length, @_c_04_avg, 
         @_c_05, @_c_05_length, @_c_05_avg, @_c_06, @_c_06_length, @_c_06_avg, @_c_07, @_c_07_length, @_c_07_avg, 
         @_c_08, @_c_08_length, @_c_08_avg, @_c_09, @_c_09_length, @_c_09_avg, @_c_10, @_c_10_length, @_c_10_avg, 
         @_c_11, @_c_11_length, @_c_11_avg, @_c_12, @_c_12_length, @_c_12_avg, @_c_13, @_c_13_length, @_c_13_avg, 
         @_c_14, @_c_14_length, @_c_14_avg, @_c_15, @_c_15_length, @_c_15_avg, @_c_16, @_c_16_length, @_c_16_avg, 
         @_c_17, @_c_17_length, @_c_17_avg, @_c_18, @_c_18_length, @_c_18_avg, @_c_19, @_c_19_length, @_c_19_avg, 
         @_c_20, @_c_20_length, @_c_20_avg, @_c_21, @_c_21_length, @_c_21_avg, @_c_22, @_c_22_length, @_c_22_avg, 
         @_c_23, @_c_23_length, @_c_23_avg
	from stats_event where device_code in (select code from base_device where f_id=@_fid) and event_code = e_code and day = e_time;
    INSERT INTO stats_event
		(device_code, event_code, day, relation, c_all, c_all_length, c_all_avg, c_00, c_00_length, 
		c_00_avg, c_01, c_01_length, c_01_avg, c_02, c_02_length, c_02_avg, c_03, c_03_length, 
		c_03_avg, c_04, c_04_length, c_04_avg, c_05, c_05_length, c_05_avg, c_06, c_06_length, 
		c_06_avg, c_07, c_07_length, c_07_avg, c_08, c_08_length, c_08_avg, c_09, c_09_length, 
		c_09_avg, c_10, c_10_length, c_10_avg, c_11, c_11_length, c_11_avg, c_12, c_12_length, 
		c_12_avg, c_13, c_13_length, c_13_avg, c_14, c_14_length, c_14_avg, c_15, c_15_length, 
		c_15_avg, c_16, c_16_length, c_16_avg, c_17, c_17_length, c_17_avg, c_18, c_18_length, 
		c_18_avg, c_19, c_19_length, c_19_avg, c_20, c_20_length, c_20_avg, c_21, c_21_length, 
		c_21_avg, c_22, c_22_length, c_22_avg, c_23, c_23_length, c_23_avg) 
	VALUES (@_fdeviceCode,e_code,e_time,@_relation,@_c_all, @_c_all_length, @_c_all_avg, @_c_00, @_c_00_length, 
	     @_c_00_avg, @_c_01, @_c_01_length, @_c_01_avg, 
         @_c_02, @_c_02_length, @_c_02_avg, @_c_03, @_c_03_length, @_c_03_avg, @_c_04, @_c_04_length, @_c_04_avg, 
         @_c_05, @_c_05_length, @_c_05_avg, @_c_06, @_c_06_length, @_c_06_avg, @_c_07, @_c_07_length, @_c_07_avg, 
         @_c_08, @_c_08_length, @_c_08_avg, @_c_09, @_c_09_length, @_c_09_avg, @_c_10, @_c_10_length, @_c_10_avg, 
         @_c_11, @_c_11_length, @_c_11_avg, @_c_12, @_c_12_length, @_c_12_avg, @_c_13, @_c_13_length, @_c_13_avg, 
         @_c_14, @_c_14_length, @_c_14_avg, @_c_15, @_c_15_length, @_c_15_avg, @_c_16, @_c_16_length, @_c_16_avg, 
         @_c_17, @_c_17_length, @_c_17_avg, @_c_18, @_c_18_length, @_c_18_avg, @_c_19, @_c_19_length, @_c_19_avg, 
         @_c_20, @_c_20_length, @_c_20_avg, @_c_21, @_c_21_length, @_c_21_avg, @_c_22, @_c_22_length, @_c_22_avg, 
         @_c_23, @_c_23_length, @_c_23_avg)
    ON DUPLICATE KEY UPDATE c_all=@_c_all, c_all_length=@_c_all_length, c_all_avg=@_c_all_avg, c_00=@_c_00, c_00_length=@_c_00_length, 
		c_00_avg=@_c_00_avg, c_01=@_c_01, c_01_length=@_c_01_length, c_01_avg=@_c_01_avg, c_02=@_c_02, c_02_length=@_c_02_length, c_02_avg=@_c_02_avg, c_03=@_c_03, c_03_length=@_c_03_length, 
		c_03_avg=@_c_03_avg, c_04=@_c_04, c_04_length=@_c_04_length, c_04_avg=@_c_04_avg, c_05=@_c_05, c_05_length=@_c_05_length, c_05_avg=@_c_05_avg, c_06=@_c_06, c_06_length=@_c_06_length, 
		c_06_avg=@_c_06_avg, c_07=@_c_07, c_07_length=@_c_07_length, c_07_avg=@_c_07_avg, c_08=@_c_08, c_08_length=@_c_08_length, c_08_avg=@_c_08_avg, c_09=@_c_09, c_09_length=@_c_09_length, 
		c_09_avg=@_c_09_avg, c_10=@_c_10, c_10_length=@_c_10_length, c_10_avg=@_c_10_avg, c_11=@_c_11, c_11_length=@_c_11_length, c_11_avg=@_c_11_avg, c_12=@_c_12, c_12_length=@_c_12_length, 
		c_12_avg=@_c_12_avg, c_13=@_c_13, c_13_length=@_c_13_length, c_13_avg=@_c_13_avg, c_14=@_c_14, c_14_length=@_c_14_length, c_14_avg=@_c_14_avg, c_15=@_c_15, c_15_length=@_c_15_length, 
		c_15_avg=@_c_15_avg, c_16=@_c_16, c_16_length=@_c_16_length, c_16_avg=@_c_16_avg, c_17=@_c_17, c_17_length=@_c_17_length, c_17_avg=@_c_17_avg, c_18=@_c_18, c_18_length=@_c_18_length, 
		c_18_avg=@_c_18_avg, c_19=@_c_19, c_19_length=@_c_19_length, c_19_avg=@_c_19_avg, c_20=@_c_20, c_20_length=@_c_20_length, c_20_avg=@_c_20_avg, c_21=@_c_21, c_21_length=@_c_21_length, 
		c_21_avg=@_c_21_avg, c_22=@_c_22, c_22_length=@_c_22_length, c_22_avg=@_c_22_avg, c_23=@_c_03, c_23_length=@_c_23_length, c_23_avg=@_c_23_avg;
  #循环执行找上级 
  UNTIL @_ftype=20801
  End repeat;
END



CREATE DEFINER=`root`@`%` PROCEDURE `CalculateEvent`(IN `d_code` varchar(32),IN `e_code` varchar(32),IN `e_time` timestamp)
BEGIN
  # 计算上级设备的事件数
  set @_fdeviceCode = d_code;
  repeat  
    # 取得上级设备
    select id,code, type into @_fid,@_fdeviceCode,@_ftype from base_device where id = (select f_id from base_device where code = @_fdeviceCode);
    # 计算上级设备事件数
    select sum(value) into @_fvalue from data_realtime where device_code in (select code from base_device where f_id=@_fid) and event_code = e_code;
    INSERT INTO data_realtime(device_code,event_code,event_time,relation,value) VALUES (@_fdeviceCode,e_code,e_time,20901,@_fvalue)
      ON DUPLICATE KEY UPDATE event_time=e_time,value=@_fvalue;
  UNTIL @_ftype=20801
  End repeat;
  
END







#车密度表 
CREATE TABLE `data_density` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `device_code` varchar(32) NOT NULL COMMENT '设备编码',
  `event_type` varchar(32) NOT NULL COMMENT '事件类型;tms.event.type',
  `event_code` varchar(32) NOT NULL COMMENT '事件代码；tms.event.code',
  `event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '事件时间',
  `vehicle_count` int(11) DEFAULT NULL COMMENT '车辆数量',
  `file_url` varchar(256) DEFAULT NULL COMMENT '图片文件url',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5246 DEFAULT CHARSET=utf8 COMMENT='密度表';

#在插入密度事件后执行
CREATE TRIGGER `density_after_insert` AFTER INSERT ON `data_density` FOR EACH ROW BEGIN
  # 取得设备的relation
  select relation into @_relation from base_device where code= new.device_code;

  SET @_time = DATE_FORMAT(new.event_time,'%H');
  SET @_day = DATE_FORMAT(new.event_time,'%Y-%m-%d');
  case @_time
      when '00' then
        INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_00,c_00_length,c_00_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,new.vehicle_count,new.vehicle_count,1,new.vehicle_count,new.vehicle_count) 
				ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+new.vehicle_count,c_all_avg = c_all_length/c_all,c_00 = c_00 + 1,c_00_length = c_00_length+new.vehicle_count,c_00_avg = c_00_length/c_00;
      when '01' then 
        INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_01,c_01_length,c_01_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,new.vehicle_count,new.vehicle_count,1,new.vehicle_count,new.vehicle_count) 
				ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+new.vehicle_count,c_all_avg = c_all_length/c_all,c_01 = c_01 + 1,c_01_length = c_01_length+new.vehicle_count,c_01_avg = c_01_length/c_01;
      when '02' then 
        INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_02,c_02_length,c_02_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,new.vehicle_count,new.vehicle_count,1,new.vehicle_count,new.vehicle_count) 
				ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+new.vehicle_count,c_all_avg = c_all_length/c_all,c_02 = c_02 + 1,c_02_length = c_02_length+new.vehicle_count,c_02_avg = c_02_length/c_02;
      when '03' then 
        INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_03,c_03_length,c_03_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,new.vehicle_count,new.vehicle_count,1,new.vehicle_count,new.vehicle_count) 
				ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+new.vehicle_count,c_all_avg = c_all_length/c_all,c_03 = c_03 + 1,c_03_length = c_03_length+new.vehicle_count,c_03_avg = c_03_length/c_03;
      when '04' then 
        INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_04,c_00_length,c_04_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,new.vehicle_count,new.vehicle_count,1,new.vehicle_count,new.vehicle_count) 
				ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+new.vehicle_count,c_all_avg = c_all_length/c_all,c_04 = c_04 + 1,c_04_length = c_04_length+new.vehicle_count,c_04_avg = c_04_length/c_04;
      when '05' then 
        INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_05,c_05_length,c_05_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,new.vehicle_count,new.vehicle_count,1,new.vehicle_count,new.vehicle_count) 
				ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+new.vehicle_count,c_all_avg = c_all_length/c_all,c_05 = c_05 + 1,c_05_length = c_05_length+new.vehicle_count,c_05_avg = c_05_length/c_05;
      when '06' then 
        INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_06,c_06_length,c_06_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,new.vehicle_count,new.vehicle_count,1,new.vehicle_count,new.vehicle_count) 
				ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+new.vehicle_count,c_all_avg = c_all_length/c_all,c_06 = c_06 + 1,c_06_length = c_06_length+new.vehicle_count,c_06_avg = c_06_length/c_06;
      when '07' then 
        INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_07,c_07_length,c_07_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,new.vehicle_count,new.vehicle_count,1,new.vehicle_count,new.vehicle_count) 
				ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+new.vehicle_count,c_all_avg = c_all_length/c_all,c_07 = c_07 + 1,c_07_length = c_07_length+new.vehicle_count,c_07_avg = c_07_length/c_07;
      when '08' then 
        INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_08,c_08_length,c_08_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,new.vehicle_count,new.vehicle_count,1,new.vehicle_count,new.vehicle_count) 
				ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+new.vehicle_count,c_all_avg = c_all_length/c_all,c_08 = c_08 + 1,c_08_length = c_08_length+new.vehicle_count,c_08_avg = c_08_length/c_08;
      when '09' then 
        INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_09,c_09_length,c_09_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,new.vehicle_count,new.vehicle_count,1,new.vehicle_count,new.vehicle_count) 
				ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+new.vehicle_count,c_all_avg = c_all_length/c_all,c_09 = c_09 + 1,c_09_length = c_09_length+new.vehicle_count,c_09_avg = c_09_length/c_09;
      when '10' then 
        INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_10,c_10_length,c_10_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,new.vehicle_count,new.vehicle_count,1,new.vehicle_count,new.vehicle_count) 
				ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+new.vehicle_count,c_all_avg = c_all_length/c_all,c_10 = c_10 + 1,c_10_length = c_10_length+new.vehicle_count,c_10_avg = c_10_length/c_10;
      when '11' then 
        INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_11,c_11_length,c_11_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,new.vehicle_count,new.vehicle_count,1,new.vehicle_count,new.vehicle_count) 
				ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+new.vehicle_count,c_all_avg = c_all_length/c_all,c_11 = c_11 + 1,c_11_length = c_11_length+new.vehicle_count,c_11_avg = c_11_length/c_11;
      when '12' then 
        INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_12,c_12_length,c_12_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,new.vehicle_count,new.vehicle_count,1,new.vehicle_count,new.vehicle_count) 
				ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+new.vehicle_count,c_all_avg = c_all_length/c_all,c_12 = c_12 + 1,c_12_length = c_12_length+new.vehicle_count,c_12_avg = c_12_length/c_12;
      when '13' then 
        INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_13,c_13_length,c_13_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,new.vehicle_count,new.vehicle_count,1,new.vehicle_count,new.vehicle_count) 
				ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+new.vehicle_count,c_all_avg = c_all_length/c_all,c_13 = c_13 + 1,c_13_length = c_13_length+new.vehicle_count,c_13_avg = c_13_length/c_13;
      when '14' then 
        INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_14,c_14_length,c_14_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,new.vehicle_count,new.vehicle_count,1,new.vehicle_count,new.vehicle_count) 
				ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+new.vehicle_count,c_all_avg = c_all_length/c_all,c_14 = c_14 + 1,c_14_length = c_14_length+new.vehicle_count,c_14_avg = c_14_length/c_14;
      when '15' then 
        INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_15,c_15_length,c_15_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,new.vehicle_count,new.vehicle_count,1,new.vehicle_count,new.vehicle_count) 
				ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+new.vehicle_count,c_all_avg = c_all_length/c_all,c_15 = c_15 + 1,c_15_length = c_15_length+new.vehicle_count,c_15_avg = c_15_length/c_15;
      when '16' then 
        INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_16,c_16_length,c_16_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,new.vehicle_count,new.vehicle_count,1,new.vehicle_count,new.vehicle_count) 
				ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+new.vehicle_count,c_all_avg = c_all_length/c_all,c_16 = c_16 + 1,c_16_length = c_16_length+new.vehicle_count,c_16_avg = c_16_length/c_16;
      when '17' then 
        INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_17,c_17_length,c_17_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,new.vehicle_count,new.vehicle_count,1,new.vehicle_count,new.vehicle_count) 
				ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+new.vehicle_count,c_all_avg = c_all_length/c_all,c_17 = c_17 + 1,c_17_length = c_17_length+new.vehicle_count,c_17_avg = c_17_length/c_17;
      when '18' then 
        INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_18,c_18_length,c_18_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,new.vehicle_count,new.vehicle_count,1,new.vehicle_count,new.vehicle_count) 
				ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+new.vehicle_count,c_all_avg = c_all_length/c_all,c_18 = c_18 + 1,c_18_length = c_18_length+new.vehicle_count,c_18_avg = c_18_length/c_18;
      when '19' then 
        INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_19,c_19_length,c_19_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,new.vehicle_count,new.vehicle_count,1,new.vehicle_count,new.vehicle_count) 
				ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+new.vehicle_count,c_all_avg = c_all_length/c_all,c_19 = c_19 + 1,c_19_length = c_19_length+new.vehicle_count,c_19_avg = c_19_length/c_19;
      when '20' then 
        INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_20,c_20_length,c_20_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,new.vehicle_count,new.vehicle_count,1,new.vehicle_count,new.vehicle_count) 
				ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+new.vehicle_count,c_all_avg = c_all_length/c_all,c_20 = c_20 + 1,c_20_length = c_20_length+new.vehicle_count,c_20_avg = c_20_length/c_20;
      when '21' then 
        INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_21,c_21_length,c_21_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,new.vehicle_count,new.vehicle_count,1,new.vehicle_count,new.vehicle_count) 
				ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+new.vehicle_count,c_all_avg = c_all_length/c_all,c_21 = c_21 + 1,c_21_length = c_21_length+new.vehicle_count,c_21_avg = c_21_length/c_21;
      when '22' then 
        INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_22,c_22_length,c_22_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,new.vehicle_count,new.vehicle_count,1,new.vehicle_count,new.vehicle_count) 
				ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+new.vehicle_count,c_all_avg = c_all_length/c_all,c_22 = c_22 + 1,c_22_length = c_22_length+new.vehicle_count,c_22_avg = c_22_length/c_22;
      when '23' then 
        INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_23,c_23_length,c_23_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,new.vehicle_count,new.vehicle_count,1,new.vehicle_count,new.vehicle_count) 
				ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+new.vehicle_count,c_all_avg = c_all_length/c_all,c_23 = c_23 + 1,c_23_length = c_23_length+new.vehicle_count,c_23_avg = c_23_length/c_23;
      
    End case;

    INSERT INTO data_realtime(device_code,event_code,event_time,relation,value,data_id) VALUES(new.device_code,new.event_code,new.event_time,@_relation,new.vehicle_count,new.id) 
        ON DUPLICATE KEY UPDATE event_time=new.event_time,value=new.vehicle_count,data_id=new.id;
    Call CalculateStatsEvent(new.device_code,new.event_code,@_day);
END;




#事件表
CREATE TABLE `data_event` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `device_code` varchar(32) NOT NULL COMMENT '设备编码',
  `event_type` varchar(32) NOT NULL COMMENT '事件类型;tms.event.type',
  `event_code` varchar(32) NOT NULL COMMENT '事件代码；tms.event.code',
  `event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '事件时间',
  `vehicle_id` varchar(32) DEFAULT NULL COMMENT '车辆ID',
  `stop_time` datetime DEFAULT NULL COMMENT '停车时间',
  `leave_time` datetime DEFAULT NULL COMMENT '离开时间',
  `duration` int(11) DEFAULT NULL COMMENT '停车时长(s)',
  `state` int(11) DEFAULT '0' COMMENT '状态： 0-告警，1-取消告警,2-手动取消告警',
  `description` varchar(256) DEFAULT '' COMMENT '备注',
  PRIMARY KEY (`id`),
  KEY `device_code` (`device_code`,`vehicle_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1408 DEFAULT CHARSET=utf8 COMMENT='事件表';

#插入后执行触发器
CREATE TRIGGER `event_after_insert` AFTER INSERT ON `data_event` FOR EACH ROW BEGIN
  # 取得设备的relation
  select relation into @_relation from base_device where code= new.device_code;

  SET @_time = DATE_FORMAT(new.event_time,'%H');
  SET @_day = DATE_FORMAT(new.event_time,'%Y-%m-%d');
  case @_time
      when '00' then
        INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_00,c_00_length,c_00_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1) 
				ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_00 = c_00 + 1,c_00_length = c_00_length+1,c_00_avg = c_00_length/c_00;
      when '01' then 
        INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_01,c_01_length,c_01_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1) 
				ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_01 = c_01 + 1,c_01_length = c_01_length+1,c_01_avg = c_01_length/c_01;
      when '02' then 
        INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_02,c_02_length,c_02_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1) 
				ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_02 = c_02 + 1,c_02_length = c_02_length+1,c_02_avg = c_02_length/c_02;
      when '03' then 
        INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_03,c_03_length,c_03_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1) 
				ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_03 = c_03 + 1,c_03_length = c_03_length+1,c_03_avg = c_03_length/c_03;
      when '04' then 
        INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_04,c_00_length,c_04_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1) 
				ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_04 = c_04 + 1,c_04_length = c_04_length+1,c_04_avg = c_04_length/c_04;
      when '05' then 
        INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_05,c_05_length,c_05_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1) 
				ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_05 = c_05 + 1,c_05_length = c_05_length+1,c_05_avg = c_05_length/c_05;
      when '06' then 
        INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_06,c_06_length,c_06_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1) 
				ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_06 = c_06 + 1,c_06_length = c_06_length+1,c_06_avg = c_06_length/c_06;
      when '07' then 
        INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_07,c_07_length,c_07_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1) 
				ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_07 = c_07 + 1,c_07_length = c_07_length+1,c_07_avg = c_07_length/c_07;
      when '08' then 
        INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_08,c_08_length,c_08_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1) 
				ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_08 = c_08 + 1,c_08_length = c_08_length+1,c_08_avg = c_08_length/c_08;
      when '09' then 
        INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_09,c_09_length,c_09_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1) 
				ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_09 = c_09 + 1,c_09_length = c_09_length+1,c_09_avg = c_09_length/c_09;
      when '10' then 
        INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_10,c_10_length,c_10_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1) 
				ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_10 = c_10 + 1,c_10_length = c_10_length+1,c_10_avg = c_10_length/c_10;
      when '11' then 
        INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_11,c_11_length,c_11_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1) 
				ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_11 = c_11 + 1,c_11_length = c_11_length+1,c_11_avg = c_11_length/c_11;
      when '12' then 
        INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_12,c_12_length,c_12_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1) 
				ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_12 = c_12 + 1,c_12_length = c_12_length+1,c_12_avg = c_12_length/c_12;
      when '13' then 
        INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_13,c_13_length,c_13_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1) 
				ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_13 = c_13 + 1,c_13_length = c_13_length+1,c_13_avg = c_13_length/c_13;
      when '14' then 
        INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_14,c_14_length,c_14_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1) 
				ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_14 = c_14 + 1,c_14_length = c_14_length+1,c_14_avg = c_14_length/c_14;
      when '15' then 
        INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_15,c_15_length,c_15_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1) 
				ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_15 = c_15 + 1,c_15_length = c_15_length+1,c_15_avg = c_15_length/c_15;
      when '16' then 
        INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_16,c_16_length,c_16_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1) 
				ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_16 = c_16 + 1,c_16_length = c_16_length+1,c_16_avg = c_16_length/c_16;
      when '17' then 
        INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_17,c_17_length,c_17_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1) 
				ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_17 = c_17 + 1,c_17_length = c_17_length+1,c_17_avg = c_17_length/c_17;
      when '18' then 
        INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_18,c_18_length,c_18_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1) 
				ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_18 = c_18 + 1,c_18_length = c_18_length+1,c_18_avg = c_18_length/c_18;
      when '19' then 
        INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_19,c_19_length,c_19_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1) 
				ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_19 = c_19 + 1,c_19_length = c_19_length+1,c_19_avg = c_19_length/c_19;
      when '20' then 
        INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_20,c_20_length,c_20_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1) 
				ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_20 = c_20 + 1,c_20_length = c_20_length+1,c_20_avg = c_20_length/c_20;
      when '21' then 
        INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_21,c_21_length,c_21_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1) 
				ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_21 = c_21 + 1,c_21_length = c_21_length+1,c_21_avg = c_21_length/c_21;
      when '22' then 
        INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_22,c_22_length,c_22_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1) 
				ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_22 = c_22 + 1,c_22_length = c_22_length+1,c_22_avg = c_22_length/c_22;
      when '23' then 
        INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_23,c_23_length,c_23_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1) 
				ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_23 = c_23 + 1,c_23_length = c_23_length+1,c_23_avg = c_23_length/c_23;
      
    End case;

    INSERT INTO data_realtime(device_code,event_code,event_time,relation,value,data_id) VALUES(new.device_code,new.event_code,new.event_time,@_relation ,1,new.id) 
          ON DUPLICATE KEY UPDATE event_time=new.event_time,value=value+1,data_id=new.id;

#执行存储过程
    Call CalculateEvent(new.device_code,new.event_code,new.event_time);
    Call CalculateStatsEvent(new.device_code,new.event_code,@_day);
END;

#更新前执行
CREATE TRIGGER `event_before_update` BEFORE UPDATE ON `data_event` FOR EACH ROW BEGIN
  SET @_duration = TIMESTAMPDIFF(SECOND,new.stop_time,new.leave_time);
  if(@_duration > 0) then
    SET new.duration = @_duration;
    SET new.state=1;
  end if;
END;









#实时数据表的前置触发器before和after 都是当事件类型是密度事件时，更新密度状态（根据阈值计算）
CREATE TABLE `data_realtime` (
  `device_code` varchar(32) NOT NULL COMMENT '设备编码',
  `event_code` varchar(32) NOT NULL,
  `event_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '事件时间',
  `relation` int(11) NOT NULL DEFAULT '0' COMMENT '关联类型；tms.device.relation',
  `value` int(11) DEFAULT '0' COMMENT '值',
  `state` int(11) DEFAULT NULL COMMENT '密度状态',
  `data_id` int(11) DEFAULT NULL COMMENT '数据ID',
  PRIMARY KEY (`device_code`,`event_code`,`relation`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='实时数据表';

CREATE TRIGGER `realtime_before_insert` BEFORE INSERT ON `data_realtime` FOR EACH ROW BEGIN
  SET @_deviceCode = new.device_code;
  SET @_eventCode = new.event_code;
  SET @_value = new.value;
  if @_eventCode='2' then
    # 密度事件
    select threshold1,threshold2 into @_threshold1,@_threshold2 from base_device where code = @_deviceCode;
    if @_value <= @_threshold1 then 
      set new.state = 0;
    end if;
    if @_value > @_threshold1 and @_value < @_threshold2 then 
      set new.state = 1;
    end if;
    if @_value >= @_threshold2 then 
      set new.state = 2;
    end if;
  End if;
END;

CREATE TRIGGER `realtime_before_update` BEFORE UPDATE ON `data_realtime` FOR EACH ROW BEGIN
  SET @_deviceCode = new.device_code;
  SET @_eventCode = new.event_code;
  SET @_value = new.value;
  if @_eventCode='2' then
    # 密度事件
    select threshold1,threshold2 into @_threshold1,@_threshold2 from base_device where code = @_deviceCode;
    if @_value <= @_threshold1 then 
      set new.state = 0;
    end if;
    if @_value > @_threshold1 and @_value < @_threshold2 then 
      set new.state = 1;
    end if;
    if @_value >= @_threshold2 then 
      set new.state = 2;
    end if;
  End if;
END;