触发器如何编写？
第一次写触发器…请高手不要见笑…
我想实现的就是表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;
欢迎大家阅读《触发器怎么编写》,跪求各位点评，by 搞代码
触发器
- 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); -> $$ Query OK, 3 rows affected (0.03 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> 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: 0 mysql> select * from px; +----+------+ | id | qty | +----+------+ | 1 | 1820 | | 2 | 1814 | | 3 | 1025 | +----+------+ 3 rows in set (0.00 sec) mysql>搞代码网（gaodaima.com）提供的所有资源部分来自互联网，如果有侵犯您的版权或其他权益，请说明详细缘由并提供版权或权益证明然后发送到邮箱[email protected]，我们会在看到邮件的第一时间内为您处理，或直接联系QQ：872152909。本网站采用BY-NC-SA协议进行授权
转载请注明原文链接：触发器怎么编写
