触发器的使用

/*
Navicat MySQL Data Transfer

Source Server         : 广西
Source Server Version : 50637
Source Host           : localhost:3306
Source Database       : c79740000011

Target Server Type    : MYSQL
Target Server Version : 50637
File Encoding         : 65001

Date: 2018-01-17 17:18:08
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for gym_goods
-- ----------------------------
DROP TABLE IF EXISTS `gym_goods`;
CREATE TABLE `gym_goods` (
`goods_id`  int(11) NOT NULL ,
`goods_name`  varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL ,
`goods_num`  int(11) NULL DEFAULT NULL ,
PRIMARY KEY (`goods_id`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=latin1 COLLATE=latin1_swedish_ci

;

-- ----------------------------
-- Records of gym_goods
-- ----------------------------
BEGIN;
INSERT INTO `gym_goods` VALUES ('1', 'opp', '10'), ('2', 'ipp', '10');
COMMIT;

-- ----------------------------
-- Table structure for gym_order
-- ----------------------------
DROP TABLE IF EXISTS `gym_order`;
CREATE TABLE `gym_order` (
`order_id`  int(11) NOT NULL ,
`goods_id`  int(11) NULL DEFAULT NULL ,
`order_num`  int(11) NULL DEFAULT NULL ,
PRIMARY KEY (`order_id`),
FOREIGN KEY (`goods_id`) REFERENCES `gym_goods` (`goods_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
INDEX `fk_goods_id` (`goods_id`) USING BTREE 
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=latin1 COLLATE=latin1_swedish_ci

;

-- ----------------------------
-- Records of gym_order
-- ----------------------------
BEGIN;
INSERT INTO `gym_order` VALUES ('6', '2', '10');
COMMIT;
DROP TRIGGER IF EXISTS `trigger_add_order`;
DELIMITER ;;
CREATE TRIGGER `trigger_add_order` AFTER INSERT ON `gym_order` FOR EACH ROW BEGIN
    update gym_goods set goods_num=goods_num-new.order_num where goods_id=new.goods_id;
end
;;
DELIMITER ;
DROP TRIGGER IF EXISTS `trigger_update_order`;
DELIMITER ;;
CREATE TRIGGER `trigger_update_order` AFTER UPDATE ON `gym_order` FOR EACH ROW BEGIN
    -- 先删
    update gym_goods set goods_num=goods_num+old.order_num where goods_id=old.goods_id;
    -- 再加
    update gym_goods set goods_num=goods_num-new.order_num where goods_id=new.goods_id;
end
;;
DELIMITER ;
DROP TRIGGER IF EXISTS `trigger_rose_order`;
DELIMITER ;;
CREATE TRIGGER `trigger_rose_order` AFTER DELETE ON `gym_order` FOR EACH ROW BEGIN
    update gym_goods set goods_num=goods_num+old.order_num where goods_id=old.goods_id;
end
;;
DELIMITER ;



--  ///////////////////////////////////////////////////////////测试///////////////////////////////////////////////////////////////////

现有两张表 商品表 goods 和订单表 order 来说明触发器的工作原理；
    
drop table if exists gym_order;
drop table if exists gym_goods;
create table gym_goods(
    goods_id int (11) PRIMARY key,
    goods_name varchar(255),
    goods_num int(11)
);
create table gym_order(
    order_id int(11) PRIMARY key,
    goods_id int(11) default NULL,
    order_num int(11) default NULL,
    constraint fk_goods_id FOREIGN key(goods_id) REFERENCES gym_goods(goods_id)
);
insert into gym_goods values('1','opp','10');
insert into gym_goods values('2','ipp','20');

insert into gym_order values('001','1','1');
insert into gym_order values('002','2','3');

-- 新增
drop trigger if exists trigger_add_order;
create trigger trigger_add_order
after insert on gym_order
for each ROW
BEGIN
    update gym_goods set goods_num=goods_num-new.order_num where goods_id=new.goods_id;
end;
INSERT into gym_order values('6','2','2');

-- 撤销
drop trigger if exists trigger_rose_order;
create trigger trigger_rose_order
after delete on gym_order
for each ROW
BEGIN
    update gym_goods set goods_num=goods_num+old.order_num where goods_id=old.goods_id;
end;

delete from gym_order where order_id='6';

-- 修改
drop trigger if exists trigger_update_order;
create trigger trigger_update_order
after update on gym_order
for each ROW
BEGIN
    -- 先删
    update gym_goods set goods_num=goods_num+old.order_num where goods_id=old.goods_id;
    -- 再加
    update gym_goods set goods_num=goods_num-new.order_num where goods_id=new.goods_id;
end;

INSERT into gym_order values('6','2','2');
update gym_order set order_num='10' where order_id='6';