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 ;