商品数据版本表设计

2016-01-26 10:48  866人阅读  评论 (0)
Tags: mysql

商品数据版本

CREATE TABLE `dotcoo_goods` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '商品ID',
  `version` int(11) NOT NULL COMMENT '版本',
  `name` varchar(45) NOT NULL COMMENT '商品名称',
  `price` decimal(10,2) NOT NULL COMMENT '商品价格',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='dotcoo 商品表';

create table `dotcoo_goods_remove` like `dotcoo_goods`;

create table `dotcoo_goods_version` like `dotcoo_goods`;

ALTER TABLE `test`.`dotcoo_goods_version` 
DROP PRIMARY KEY,
ADD PRIMARY KEY (`id`, `version`)  COMMENT '';

USE `test`;

DELIMITER $$

DROP TRIGGER IF EXISTS test.dotcoo_goods_AFTER_INSERT$$
USE `test`$$
CREATE DEFINER = CURRENT_USER TRIGGER `test`.`dotcoo_goods_AFTER_INSERT` AFTER INSERT ON `dotcoo_goods` FOR EACH ROW
BEGIN
    insert into `dotcoo_goods_version` select * from `dotcoo_goods` where id = new.id;
END
$$
DELIMITER ;
USE `test`;

DELIMITER $$

DROP TRIGGER IF EXISTS test.dotcoo_goods_AFTER_UPDATE$$
USE `test`$$
CREATE DEFINER = CURRENT_USER TRIGGER `test`.`dotcoo_goods_AFTER_UPDATE` AFTER UPDATE ON `dotcoo_goods` FOR EACH ROW
BEGIN
    insert into `dotcoo_goods_version` select * from `dotcoo_goods` where id = new.id;
END
$$
DELIMITER ;
USE `test`;

DELIMITER $$

DROP TRIGGER IF EXISTS test.dotcoo_goods_BEFORE_DELETE$$
USE `test`$$
CREATE DEFINER = CURRENT_USER TRIGGER `test`.`dotcoo_goods_BEFORE_DELETE` BEFORE DELETE ON `dotcoo_goods` FOR EACH ROW
BEGIN
    insert into `dotcoo_goods_remove` select * from `dotcoo_goods` where id = old.id;
END
$$
DELIMITER ;