7.6 触发器（Trigger）

学习目标：

1.理解什么是触发器？

2.掌握触发器的四个要素。

3.能根据任务需求逻辑创建触发器。

一、理解触发器

1.what •触发器是一种特殊的存储过程

2.why：可以用来对表实施复杂的完整性约束，保持数据的一致性。

3.where：数据增删改操作中

4.如何创建触发器

CREATE TRIGGER 触发器名
时机  -- 3.before、after
操作  -- 2. 增（INSERT）、删（delete）、改（update）
ON 表  -- 1. 监听谁？
FOR EACH ROW
BEGIN
  # 过程体  4.触发的事件
END;

二、问题导入

1.注册用户的手机号码必须是11位，否则，改成’13800000000’,提醒更新电话号码。

四要素：

(1)监听谁？表

(2)发生的事？增（insert）删（delete）改（update）

(3)触发时机？before|after

(4)触发的事件？过程体执行语句

insert添加数据描述：

delete删除数据描述

update更新数据描述

DROP TRIGGER if EXISTS tri_tel;
CREATE TRIGGER tri_tel
BEFORE     -- 时机？
INSERT      -- 什么事？
ON customer  -- 监听谁？
FOR EACH ROW
begin  -- 触发的事件
    -- 判断新增的数据，手机是否11位，如果不是……
    if CHAR_LENGTH(new.tel) != 11 OR new.tel is null
    then
      SET new.tel = '13800000000';
    -- 不能写查询    SELECT '手机不正确，请尽快更新！';
    end if;    
end;

二、customer表中添加新用户，判断输入的密码长度，若长度小于6，则拒绝插入新用户，并提示“密码长度小于6，请重新输入“。若长度大于等于6，则按md5算法对密码加密处理。

CREATE TRIGGER tri_users_in
BEFORE     -- 3.触发时机？  before、after
INSERT     -- 2.什么操作？增、删、改其中一项
ON users   -- 1.监听谁？表
FOR EACH ROW # 触发事件，新增对象为new
-- INSERT users(ulogin,uname,upwd)
-- VALUES('13872192005','刘群娣','123');
BEGIN      -- 4.触发什么事件？
        if CHAR_LENGTH(new.upwd)>=6 then
                SET new.upwd = MD5(new.upwd);
        else
             SIGNAL SQLSTATE '45000'
             SET message_text = '密码少于6位';
        end if    ;
END;

INSERT users(ulogin,uname,upwd)
VALUES('13872192005','刘群娣','123');

结果：

INSERT users(ulogin,uname,upwd)
VALUES('13872192005','刘群娣','123abc');

三、情景应用

需求：当用户选购好商品之后，完成新增订单信息。

— 分析：解决思路

— 当ordersdetail选购商品后（新增），对应的支付金额需要在orders更新

— 同时更新product表的销售数量

-- 需求：当用户选购好商品之后，并完成了订单
-- 分析：解决思路
-- 当ordersdetail选购商品后（新增），对应的支付金额需要在orders更新
-- 同时更新product表的销售数量
DROP TRIGGER if exists tri_orders_in;
CREATE TRIGGER tri_orders_in 
AFTER -- 暂时时机 AFTER 
INSERT 
ON ordersdetail  -- 新增数据  
FOR EACH ROW
BEGIN
 DECLARE price decimal(10,2);-- price 表示新增商品的单价
 -- 过程体不能使用查询语句
  SELECT p.currentPrice into price
    FROM product p
    WHERE p.productID = new.productID;
--  DESC product;
  -- 修改对应new.ordersID订单的金额    
    UPDATE orders o SET amount = amount + new.quantity*price
    WHERE o.ordersID = new.ordersID;
    -- 更新product表，对应商品的销售数量
    UPDATE product p
    set p.salesCount = p.salesCount + new.quantity
  WHERE p.productID    = new.productID;
end;

新增订单商品之前：

新增订单商品之后：

--  需求：当用户选购好商品之后，并完成了订单
-- 实现触发器
DROP TRIGGER if EXISTS trig_oi_in;
CREATE TRIGGER trig_oi_in
AFTER        -- 3. 时机
INSERT       -- 2. 操作
ON ordersitem  -- 1.监听表
FOR EACH row   -- 固定
begin     -- 难点：事件
 -- 声明变量price存放商品单价
 DECLARE price decimal(20,2);
 -- 查询新增商品的单价,赋值给变量
 select gprice INTO price
 from goods where gid = new.gid;
 -- 1. 在orders表中要修改对应new.oid订单的销售额oamount
 UPDATE orders SET oamount = oamount + price * new.inum
 WHERE oid = new.oid;
 -- 2. 在goods表中要修改对应商品new.gid的库存gquantity减少，
 -- 销售量gsale_qty 增加
 UPDATE goods 
 SET gquantity = gquantity - new.inum, -- 库存gquantity减少
     gsale_qty = gsale_qty +new.inum   -- 销售量gsale_qty 增加
 WHERE      goods.gid = new.gid;
end;

新增订购前

新增订购商品

新增订购后，对应的orders表和goods表的变化