Mysql Day Thirteen

触发器

需求：有两张表，一张订单表，一张库存表，没生成一个订单，意味着库存要减少

触发器：事先为某张表绑定一段代码，当表中的某些内容发生改变时，系统自动触发代码执行

              事件类型：insert delete update

              触发实践： before after

              触发对象：表中的每一条记录

              一张表中只能拥有一种触发时间的一种类型触发器

触发器使用

触发器基本语法：

        delimiter  自定义符号   -- 零时定义结束符，后续代码中只有碰到自定义符号代码才结束

        create trigger 触发器名字 触发时间 事件类型  on 触发对象 for each row

        begin

                 -- 触发器内容

        end

        delimiter ；  -- 将零时修改修正过来

-- 创建数据库
create database  store charset utf8;
use store;

-- 创建数表
create table  my_goods(
id int primary key auto_increment,
name varchar(20) not null,
price decimal(10,2) default 1,
inv int comment "库存"
)charset utf8;

insert into my_goods values (null,"iphone6",5000,100),(null,"iphone8",7000,200);

create table my_order(
id int primary key auto_increment,
g_id varchar(20) not null comment "商品id",
g_number int comment "商品数量"
)charset utf8;


-- 创建触发器
delimiter $$
create trigger after_order after insert on my_order for each row
begin
selsect * from my_goods;
end
$$
delimiter ;

-- 查看触发器
show triggers like "";
show triggers;
*************************** 1. row ***************************
             Trigger: after_order
               Event: INSERT
               Table: my_order
           Statement: begin
update my_goods set inv = inv - 100 where id=1;
end
              Timing: AFTER
             Created: 2019-06-09 20:53:24.77
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8_general_ci
-- --------------------------------------------------------------

-- 查看触发器创建语句
show create trigger after_order;
-- 所有的触发器都会保存在一张表中：information_schema.triggers

-- 删除触发器 drop trigger 触发器名字；
drop trigger after_order;
-- --------------------------------------------------------------


-- 需求实现
-- 触发器记录：不管触发器是否触发，只要当某种操作准备执行，系统就会将当前要操作的记录的当前状态和
-- 即将执行之后新的状态分别保留下来，共触发器使用，其中当前操作状态保存在old中，新状态保存在new中

-- 创建触发器
delimiter $$
create trigger after_order after insert on my_order for each row
begin
update my_goods set inv = inv - new.g_number  where id = new.g_id ;
end
$$
delimiter ;

select * from my_goods;
+----+---------+---------+------+
| id | name    | price   | inv  |
+----+---------+---------+------+
|  1 | iphone6 | 5000.00 |  100 |
|  2 | iphone8 | 7000.00 |  200 |
+----+---------+---------+------+

select * from my_order;
Empty set (0.00 sec)

insert into my_order values(null,'1',5);

select * from my_order;
+----+------+----------+
| id | g_id | g_number |
+----+------+----------+
|  1 | 1    |        5 |
+----+------+----------+

select * from my_goods;
+----+---------+---------+------+
| id | name    | price   | inv  |
+----+---------+---------+------+
|  1 | iphone6 | 5000.00 |   95 |
|  2 | iphone8 | 7000.00 |  200 |
+----+---------+---------+------+