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

drop table if exists `testtable_trc`;
CREATE TABLE `testtable_trc` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `id_device` bigint(20) DEFAULT NULL,
  `dt` datetime DEFAULT NULL,
  `dt_ms` int(11) DEFAULT NULL,
  `m420` int(11) DEFAULT NULL,
  `u420` int(11) DEFAULT NULL,
  `m480` int(11) DEFAULT NULL,
  `u480` int(11) DEFAULT NULL,
  `m580` int(11) DEFAULT NULL,
  `u580` int(11) DEFAULT NULL,
  `m720` int(11) DEFAULT NULL,
  `u720` int(11) DEFAULT NULL,
  `m780` int(11) DEFAULT NULL,
  `u780` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `dt` (`dt`),
  KEY `dt_ms` (`dt_ms`),
  KEY `FK_Reference_devices_history_trc_devices` (`id_device`),
  KEY `ddt` (`id_device`,`dt`,`dt_ms`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

DELIMITER $$

DROP PROCEDURE IF EXISTS `wm_history`.`create_trc_history_temp` $$
CREATE  PROCEDURE `create_trc_history_temp`()
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
    create table `devices_trc_history_new` like `devices_trc_history`;
   
    set @rename = concat('rename table `devices_trc_history` to `devices_trc_history_',
    MONTH(ADDDATE(CURDATE(),interval -1 day))*100+DAY(ADDDATE(CURDATE(),interval -1 day)),
    '`, `devices_trc_history_new` to `devices_trc_history`');
   PREPARE name FROM @rename;
   EXECUTE name;
   DEALLOCATE PREPARE name;
END $$

drop trigger if exists `wm_history`.`devices_trc_history_before_ins_tr`$$
CREATE TRIGGER `wm_history`.`devices_trc_history_before_ins_tr` BEFORE INSERT ON `wm_history`.`testtable_trc`
  FOR EACH ROW
BEGIN
    update `last_signals_value_from_devices_history` set `state_u_420`=NEW.`u420`,
  `state_u_480`=NEW.`u480`, `state_u_580`=NEW.`u580`, `state_u_720`=NEW.`u720`,
  `state_u_780`=NEW.`u780` where `CAN_DEC`=NEW.`id_device`;

        CALL `create_trc_history_temp`(NEW.`dt`);

END $$
DELIMITER ;


INSERT INTO   `testtable_trc`( `id_device`, `dt`, `dt_ms`, `m420`,  `u420`,
 `m480`, `u480`, `m580`, `u580`, `m720`, `u720`,  `m780`,`u780` ) VALUE ( 456789,
  '2011-30-05 11:31:22', 559, 456, 789, 123,  489,  123,  456,  7489, 12,  0, 0 );