mysql触发器

触发器

控制连锁变化
连锁效应：牵一发而动全身

触发器的作用
监视某个表的增删改操作，实现对触发表的基本操作

（1）创建触发器的语法

如下：

CREATE TRIGGER trigger_name 
trigger_time trigger_event ON tb_name
 FOR EACH ROW 
trigger_stmt

注释如下：
trigger_name：触发器的名称
tirgger_time：触发时机，为BEFORE或者AFTER
trigger_event：触发事件，为INSERT、DELETE或者UPDATE
tb_name：表示建立触发器的表名，就是在哪张表上建立触发器
trigger_stmt：触发器的程序体，可以是一条SQL语句或者是用BEGIN和END包含的多条语句

例1：class_num表统计各班人数，创建一个触发器，当student表里每增加一个人，class_num表都可以同步更新；
代码如下：

delimiter //
Create trigger trg_1
After insert on student
For each row
Update class_num set 人数=人数+1 where classno=new.classno;
End //

（2）查看数据库下所有的触发器

1、show triggers;查看当前数据库下的触发器
2、select trigger_name,trigger_schema [*]from information_schema.triggers;

（3）删除触发器

Drop trigger 触发器名；

触发器实际应用实例

思考：
1.订单表中现有购买2号商品记录，购买了8件，购买后，对trade表有什么影响？
(增加影响剩余数量自动减少) 插入触发器 insert

2.本来购买8件2号商品，但现在要增加到10件，对trade表有什么影响？
（更改更改可能增加也可能减少）更改触发器 update
库存数量=库存数量+old.订单数量-new.订单数量
库存数量=20-8+10；

3.买家由于特殊原因要取消订单1，对trade表有什么影响？
（删除剩余数量增加）删除触发器 delete
归纳：
订单表中数据发生变化（增加、更改、删除）时，trade表会相应发生变化

问题：
如何控制这种连锁变化？用触发器 trigger。用触发器监视订单表的变化，并触发trade表的相应变化

问题1：
After购买
库存数量=库存数量-8

思考，如果开始时购买的是15件，而不是8件？能不能有统一的触发器，而不是每次都更改？

将trade set 库存数量=库存数量-new.订单数量(新插入的行用new来表示)
将where商品编号=2改为where商品编号=new.商品编号;

问题2：
本来购买8件2号宠物，但现在要增加到10件，对trade表有什么影响？
更新记录，由原先的8件改为10件
库存数量=原先库存数量+旧订单数量-新订单数量
触发时机：before,after
触发事件：update,delete,insert
before update,delete,insert
after update,delete,insert
触发事件：load data ,replace

Delimiter //
Create trigger trg2
After update on order1
For each row
Begin
Update trade set 库存数量=库存数量+old.订单数量-new.订单数量 where商品编号=old.商品编号;
End //
Delimiter ;

问题3：买家由于特殊原因要取消订单1，对trade表有什么影响？

DELIMITER //
CREATE TRIGGER trg_3
AFTER DELETE ON order1
FOR EACH ROW
BEGIN
UPDATE trade SET 库存数量=库存数量+old.订单数量 WHERE 商品编号=old.商品编号;
END //
DELIMITER ;

问题4:该商店每笔订单限购5件，该如何规定触发器
解决：用before来实现库存的检查工作，创建触发器之前请记得要把trg_1删除；

DELIMITER //
CREATE TRIGGER trg4
BEFORE INSERT ON order1
FOR EACH ROW
BEGIN
IF (new.订单数量>5) THEN 
SET new.订单数量=5;
END IF;
UPDATE trade SET 库存数量=库存数量-new.订单数量 WHERE 商品编号=new.商品编号;
END //
DELIMITER ;

问题5：思考修改上面的问题，该商店要求订单数量不能大于库存数量;又该如何去规定触发器；

抛出异常的处理：
IF (num=0) THEN
SIGNAL SQLSTATE  '45000'  SET MESSAGE_TEXT = '库存不足';

DELIMITER //
CREATE TRIGGER trg_before
BEFORE INSERT ON order1
FOR EACH ROW
BEGIN
DECLARE num INT;
SELECT 库存数量 INTO num FROM trade WHERE 
商品编号=new.商品编号;
IF (new.订单数量>num) THEN
SET new.订单数量=num;#Mysql 进行赋值的时候需要set关键词
END IF;
UPDATE trade SET 库存数量=库存数量-new.订单数量 
WHERE 商品编号=new.商品编号;
END//
DELIMITER ;