触发器如何编写？
第一次写触发器...请高手不要见笑...
我想实现的就是表px的qty字段是所有spec.pid = px.id 的qty的总和
我下面的触发器代码为什么只是将px的qty字段更新为最后插入的那个spec.qty呢？(qty_on_insert触发器)
MySQL版本
Server version: 5.1.41-community MySQL Community Server (GPL)
表结构
CREATE TABLE `px` ( `id` int(2) NOT NULL AUTO_INCREMENT, `qty` int(10) NOT NULL DEFAULT '0', PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;CREATE TABLE `spec` ( `id` int(11) NOT NULL AUTO_INCREMENT, `pid` int(8) NOT NULL, `qty` int(10) NOT NULL DEFAULT '0', PRIMARY KEY (`id`), KEY `pid` (`pid`), CONSTRAINT `spec_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `px` (`id`) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
DELIMITER $$CREATE TRIGGER `qty_on_delete` BEFORE DELETE ON `spec`FOR EACH ROW BEGINUPDATE (SELECT `pid` FROM `px`,`spec` WHERE `px`.`id` = `spec`.`pid`) AS `T1`,`px`SET `px`.`qty` = `px`.`qty` - OLD.`qty`WHERE `T1`.`pid` = `px`.`id`;END$$CREATE TRIGGER `qty_on_update` BEFORE UPDATE ON `spec`FOR EACH ROW BEGINUPDATE (SELECT `pid` FROM `px`,`spec` WHERE `px`.`id` = `spec`.`pid`) AS `T1`,`px`SET `px`.`qty` = `px`.`qty` + (NEW.`qty` - OLD.`qty`)WHERE `T1`.`pid` = `px`.`id`;END$$CREATE TRIGGER `qty_on_insert` BEFORE INSERT ON `spec`FOR EACH ROW BEGINUPDATE (SELECT `pid` FROM `px`,`spec` WHERE `px`.`id` = `spec`.`pid`) AS `T1`,`px`SET `px`.`qty` = `px`.`qty` + NEW.`qty`WHERE `T1`.`pid` = `px`.`id`;END$$DELIMITER ;
INSERT INTO `px`(`qty`) VALUES(0),(0),(0);INSERT INTO `spec`(`pid`,`qty`) VALUES(1,35),(1,63),(1,324),(1,654),(1,87),(1,657),(2,35),(2,35),(2,54),(2,365),(2,5),(2,985),(2,335),(3,315),(3,355),(3,355);
mysql> CREATE TABLE `px` ( -> `id` int(2) NOT NULL AUTO_INCREMENT, -> `qty` int(10) NOT NULL DEFAULT '0', -> PRIMARY KEY (`id`) -> ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;Query OK, 0 rows affected (0.11 sec)mysql>mysql> CREATE TABLE `spec` ( -> `id` int(11) NOT NULL AUTO_INCREMENT, -> `pid` int(8) NOT NULL, -> `qty` int(10) NOT NULL DEFAULT '0', -> PRIMARY KEY (`id`), -> KEY `pid` (`pid`), -> CONSTRAINT `spec_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `px` (`id`) ONDELETE CASCADE ON UPDATE CASCADE -> ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;Query OK, 0 rows affected (0.11 sec)mysql> DELIMITER $$mysql> CREATE TRIGGER `qty_on_delete` BEFORE DELETE ON `spec` -> FOR EACH ROW -> BEGIN -> UPDATE `px` -> SET `px`.`qty` = `px`.`qty` - OLD.`qty` -> WHERE OLD.`pid` = `px`.`id`; -> END$$Query OK, 0 rows affected (0.20 sec)mysql>mysql> CREATE TRIGGER `qty_on_update` BEFORE UPDATE ON `spec` -> FOR EACH ROW -> BEGIN -> UPDATE `px` -> SET `px`.`qty` = `px`.`qty` + (NEW.`qty` - OLD.`qty`) -> WHERE new.`pid` = `px`.`id`; -> END$$Query OK, 0 rows affected (0.06 sec)mysql>mysql> CREATE TRIGGER `qty_on_insert` BEFORE INSERT ON `spec` -> FOR EACH ROW -> BEGIN -> UPDATE `px` SET `px`.`qty` = `px`.`qty` + NEW.`qty` -> WHERE new.`pid` = `px`.`id`; -> END$$Query OK, 0 rows affected (0.05 sec)mysql> INSERT INTO `px`(`qty`) VALUES -> (0), -> (0), -> (0); -> $$Query OK, 3 rows affected (0.03 sec)Records: 3 Duplicates: 0 Warnings: 0mysql> INSERT INTO `spec`(`pid`,`qty`) VALUES -> (1,35), -> (1,63), -> (1,324), -> (1,654), -> (1,87), -> (1,657), -> (2,35), -> (2,35), -> (2,54), -> (2,365), -> (2,5), -> (2,985), -> (2,335), -> (3,315), -> (3,355), -> (3,355);Query OK, 16 rows affected (0.44 sec)Records: 16 Duplicates: 0 Warnings: 0mysql> select * from px;+----+------+| id | qty |+----+------+| 1 | 1820 || 2 | 1814 || 3 | 1025 |+----+------+3 rows in set (0.00 sec)mysql>
