触发器如何编写？
第一次写触发器...请高手不要见笑...
我想实现的就是表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)

表结构

SQL code

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;

SQL code

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 ;

SQL code

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);

SQL code

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>