DELIMITER $$
CREATE TRIGGER 触发器名称
{BEFORE|AFTER}
{INSERT|UPDATE|DELETE}
ON 事件发启的表
FOR EACH ROW -- 指定行触发器, 表示符合条件的行都会处理
BEGIN
触发后执行的语句
END;
$$
DELIMITER ;
关键字 OLD/NEW
当事件触发后触发器内产生两种数据, 操作之前的状态(OLD), 操作后的状态(NEW)
触发时间
类型
说明
BEFORE
表中数据发生改变前的状态
AFTER
表中数据发生改变后的状态
触发事件
类型
说明
OLD/NEW
INSERT
表中插入事件发生时
不包含 OLD, 包含 NEW
UPDATE
表中更新事件发生时
包含 OLD和 NEW
DELETE
表中删除事件发生时
包含 OLD, 不包含 NEW
删除语句
DROP TRIGGER IF EXISTS 数据库名称.触发器名称;
查询语句
SHOW TRIGGERS;
简单例子
准备商品表和订单表
CREATE TABLE `goods` (
`id` INT(20) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) DEFAULT NULL,
`stock` INT(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='商品表';
INSERT INTO `goods` VALUES (1, '手表', 100);
INSERT INTO `goods` VALUES (2, '金戒指', 100);
INSERT INTO `goods` VALUES (3, '台式电脑', 100);
CREATE TABLE `order` (
`id` INT(20) NOT NULL AUTO_INCREMENT,
`goods_id` INT(20) NOT NULL,
`quantity` INT(20) NOT NULL,
`memo` VARCHAR(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='订单表';
创建订单, 指定某一商品, 同时指定触发器被触发扣除指定商品库存
DELIMITER $$
CREATE TRIGGER `t1`
BEFORE
INSERT
ON `order`
FOR EACH ROW
BEGIN
-- 定义变量用于存指定商品库存
DECLARE restStock INT(20) DEFAULT 0;
-- 查询指定商品库存数
SELECT `stock` INTO restStock FROM `goods` WHERE `id`=new.`goods_id`;
-- 判断是否有库存
IF restStock = 0 THEN
-- 终止整个事件, 抛异常, 库存不足!!
SIGNAL SQLSTATE 'TX000' SET MESSAGE_TEXT = 'Insufficient stock of goods!!';
END IF;
-- 判断如果库存不够可以把剩余库存清零
IF new.`quantity` > restStock THEN
SET new.`quantity` = restStock;
END IF;
-- 消减卖出的商品库存数
UPDATE `goods` SET `stock`=`stock`-new.`quantity` WHERE `id`=new.`goods_id`;
END;
$$
DELIMITER ;
修改指定订单已购买的商品数量, 同时指定触发器被触发调整商品库存
DELIMITER $$
CREATE TRIGGER `t2`
BEFORE
UPDATE
ON `order`
FOR EACH ROW
BEGIN
-- 定义变量用于存改动后多出的商品数量
DECLARE moreQuantity INT(20) DEFAULT 0;
-- 定义变量用于存改动后多出的商品数量
DECLARE lessQuantity INT(20) DEFAULT 0;
-- 定义变量用于存指定商品库存
DECLARE restStock INT(20) DEFAULT 0;
SET moreQuantity = OLD.`quantity` - NEW.`quantity`;
SET lessQuantity = NEW.`quantity` - OLD.`quantity`;
-- 订单表更新时如果商品数未改动通过
IF moreQuantity != 0 THEN
-- 改动后的数量多于之前数量时通过
IF lessQuantity > 0 THEN
-- 查询指定商品库存数
SELECT `stock` INTO restStock FROM `goods` WHERE `id`=NEW.`goods_id`;
-- 判断指定商品库存是否少于改动后多出的商品数量
IF restStock < lessQuantity THEN
-- 抛异常, 库存不足!!, 终止整个事件
SIGNAL SQLSTATE 'TX000' SET MESSAGE_TEXT = 'Insufficient stock of goods!!';
END IF;
END IF;
-- 调整已卖出的商品库存数
UPDATE `goods` SET `stock`=`stock` + moreQuantity WHERE `id`=NEW.`goods_id`;
END IF;
END;
$$
DELIMITER ;
删除指定订单, 同时指定触发器被触发调整商品库存
DELIMITER $$
CREATE TRIGGER `t3`
AFTER
DELETE
ON `order`
FOR EACH ROW
BEGIN
-- 调整已卖出的商品库存数
UPDATE `goods` SET `stock`=`stock` + OLD.`quantity` WHERE `id`=OLD.`goods_id`;
END;
$$
DELIMITER ;