/* 这句肯定会触发触发器 */
UPDATE `px` SET `isdel` = 1 WHERE `id` = 1;

/* 下面这句呢？也会触发触发器？ */
UPDATE `px` SET `desc` = "XXOO" WHERE `id` = 2;

 
 /* 建表语句 */ 
 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); 
 INSERT INTO `goods`(`pid`,`sid`) VALUES (1,1),(1,2),(1,2),(1,3),(1,3),(1,3),(2,4),(2,5),(2,5),(2,5),(3,6),(3,7),(3,7),(3,7); 
  
 /* 添加触发器 */ 
 /* 更新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;