第一次写触发器...请高手不要见笑...
我想实现的就是表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
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 ;
插入数据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);
我想实现的就是表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
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 ;
插入数据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 索引的使用顺序是怎样的
- MYSQL1048错误到底怎么解决啊
- 求存储过程写法
- mysql中用什么分隔多个批呢?mssql中是用 go 命令,但是发现在mysql中不认呀?
- 请问mysql有中文版吗?
- mysql 6.0有无事务处理机制 也就是falcon加入了吗?
- 关于将MYSQL_ROW类型的输出问题
- 请问大家一个问题,感谢大家的进来。
- 请大神帮忙看一下,这个题目的是否跑题了?
- You have an error in your SQL syntax; check the manual that corresponds to your
- 关于索引的弱智问题,这是本人在CSDN上第一次发帖,处女座,久闻CSDN人很热心
- MySQL连接错误
-> `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);
-> $$
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>