Форум пользователей MySQL

CREATE TABLE `devices_ai220` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `id_device` bigint(20) DEFAULT NULL,
  `dt` datetime DEFAULT NULL,
  `dt_ms` int(11) DEFAULT NULL,
  `persost` int(11) DEFAULT NULL,
  `postsost` int(11) DEFAULT NULL,
  `max` int(11) DEFAULT NULL,
  `min` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `dt` (`dt`),
  KEY `dt_ms` (`dt_ms`),
  KEY `FK_Reference_devices_ai220_history_devices` (`id_device`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE DEFINER = 'root'@'localhost' PROCEDURE `ai220history`()
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
    create table `deivces_ai220_new` like `devices_ai220`;
   
    set @rename = concat('rename table `devices_ai220` to `devices_ai220_',
    MONTH(ADDDATE(CURDATE(),interval -1 day))*100+DAY(ADDDATE(CURDATE(),interval -1 day)),
    '`, `deivces_ai220_new` to `devices_ai220`');
   PREPARE name FROM @rename;
   EXECUTE name;
   DEALLOCATE PREPARE name;  
   
set @trigger =' CREATE TRIGGER `devices_ai220_before_ins_tr` BEFORE INSERT ON `devices_ai220` '+
  'FOR EACH ROW '+
'BEGIN '+
    'update `last_signals_value_from_devices_history` set `state_per`=NEW.`persost`, `state_post`=NEW.`postsost` where `CAN_DEC`=NEW.`id_device`; '+
    'if DAY(NEW.`dt`)!=DAY(CURDATE()) then '+
        'call `ai220history`; '+
    'end if; END;';
  PREPARE tr FROM @trigger;
   EXECUTE tr;
   DEALLOCATE PREPARE tr;  

END;
 

set @trigger =' CREATE TRIGGER `devices_ai220_before_ins_tr` BEFORE INSERT ON `devices_ai220` '+
  'FOR EACH ROW '+
'BEGIN '+
    'update `last_signals_value_from_devices_history` set `state_per`=NEW.`persost`, `state_post`=NEW.`postsost` where `CAN_DEC`=NEW.`id_device`; '+
    'if DAY(NEW.`dt`)!=DAY(CURDATE()) then '+
        'call `ai220history`; '+
    'end if; END;';
  PREPARE tr FROM @trigger;
   EXECUTE tr;
   DEALLOCATE PREPARE tr;  

update `last_signals_value_from_devices_history` set `state_per`=NEW.`persost`, `state_post`=NEW.`postsost` where `CAN_DEC`=NEW.`id_device`;