mysql分区表与trigger

CREATE TABLE `t_test` (
  `id` int(11) NOT NULL,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

 

 

CREATE TABLE `t_test_part` (
  `id` int(11) NOT NULL DEFAULT '0',
  `NAME` varchar(10) DEFAULT NULL,
  `import_date` date NOT NULL,
  PRIMARY KEY (`id`,`import_date`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (to_days(import_date))
(PARTITION p00 VALUES LESS THAN (734503) ENGINE = InnoDB,
 PARTITION p01 VALUES LESS THAN (734534) ENGINE = InnoDB,
 PARTITION p02 VALUES LESS THAN (734562) ENGINE = InnoDB,
 PARTITION p03 VALUES LESS THAN (734593) ENGINE = InnoDB,
 PARTITION p04 VALUES LESS THAN (734623) ENGINE = InnoDB,
 PARTITION p05 VALUES LESS THAN (734654) ENGINE = InnoDB,
 PARTITION p06 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */

 

 

DELIMITER $$

USE `virus`$$

DROP TRIGGER /*!50032 IF EXISTS */ `tri_test`$$

CREATE
    /*!50017 DEFINER = 'root'@'%' */
    TRIGGER `tri_test` AFTER INSERT ON `t_test`
    FOR EACH ROW BEGIN
    INSERT INTO t_test_part(id,NAME,import_date)
    VALUES(new.id,new.name,NOW());
    END;
$$

DELIMITER ;

 

DELIMITER $$

USE `virus`$$

DROP TRIGGER /*!50032 IF EXISTS */ `tri_test_part`$$

CREATE
    /*!50017 DEFINER = 'root'@'%' */
    TRIGGER `tri_test_part` BEFORE INSERT ON `t_test_part`
    FOR EACH ROW BEGIN
    DECLARE v_num INT;
    DECLARE v_date DATE;
    SELECT COUNT(*) INTO v_num FROM t_test_part WHERE id=new.id;
    IF v_num=0 THEN
    SET new.import_date=NOW();
    ELSE
    SELECT import_date INTO v_date FROM t_test_part WHERE id=new.id LIMIT 1;
    SET new.import_date=v_date;
    END IF;
    END;
$$

DELIMITER ;