/* 建表语句 */
DROP DATABASE IF EXISTS `testtest`;
CREATE DATABASE `testtest` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `testtest`;

DROP TABLE IF EXISTS `px`;
CREATE TABLE IF NOT EXISTS `px` (
  `id` int(2) NOT NULL AUTO_INCREMENT,
  `desc` varchar(10) CHARACTER SET gbk DEFAULT NULL,
  `isdel` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除(0:不删除|1:删除)',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

DROP TABLE IF EXISTS `spec`;
CREATE TABLE IF NOT EXISTS `spec` (
  `id` int(2) NOT NULL AUTO_INCREMENT,
  `pid` int(2) NOT NULL,
  `isdel` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除(0:不删除|1:删除)',
  PRIMARY KEY (`id`),
  KEY `pact_id` (`pid`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

DROP TABLE IF EXISTS `goods`;
CREATE TABLE IF NOT EXISTS `goods` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `pid` int(2) NOT NULL,
  `sid` int(2) NOT NULL,
  `isdel` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除(0:不删除|1:删除)',
  PRIMARY KEY (`id`),
  KEY `pid` (`pid`),
  KEY `sid` (`sid`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

/* 添加约束 */
ALTER TABLE `goods`
  ADD CONSTRAINT `goods_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `px` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `goods_ibfk_2` FOREIGN KEY (`sid`) REFERENCES `spec` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `spec`
  ADD CONSTRAINT `spec_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `px` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
COMMIT;

/* 插入数据 */
INSERT INTO `px`(`isdel`) VALUES (0),(0),(0);
INSERT INTO `spec`(`pid`) VALUES (1),(1),(1),(2),(2),(3),(3);

/* 添加触发器 */
/* 更新px的isdel字段时，更新spec和goods的isdel字段 */
DELIMITER $$
CREATE TRIGGER `hidden_px` BEFORE UPDATE ON `px`
FOR EACH ROW
BEGIN
UPDATE `spec`,`goods`
SET `spec`.`isdel` = NEW.`isdel`,`goods`.`isdel` = NEW.`isdel`
WHERE NEW.`id` = `spec`.`pid` AND NEW.`id` = `goods`.`pid`;
END$$
DELIMITER ;
COMMIT;