Mysql触发器

简介

• 触发器是由表事件来触发的特殊存储过程, 如某表操作(增删改)时, 自动执行预先编译好的 SQL语句, 也就是与普通存储过程不同之处是不是主动调用的

创建语句

• 每个表最多创建6个触发器, BEFORE/AFTER INSERT, BEFORE/AFTER UPDATE, BEFORE/AFTER DELETE
• 触发器名称在一个表中唯一, 但在一个数据库中可以重复

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;

简单例子

1. 准备商品表和订单表

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='订单表';

2. 创建订单, 指定某一商品, 同时指定触发器被触发扣除指定商品库存

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 ;

3. 修改指定订单已购买的商品数量, 同时指定触发器被触发调整商品库存

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 ;

4. 删除指定订单, 同时指定触发器被触发调整商品库存

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 ;

如果您觉得有帮助，欢迎点赞哦 ~ 谢谢!!