触发器怎么编写

触发器如何编写？

第一次写触发器…请高手不要见笑…

我想实现的就是表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 
BEGIN
UPDATE (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 
BEGIN
UPDATE (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 
BEGIN
UPDATE (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`) ON
DELETE 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);