MySQL 触发器学习-markdown->html 格式测试

<!doctype html>触发器

触发器(Trigger)

目标

  • 定义
  • 应用场景
  • 创建语法
  • 创建简单触发器

定义

理解: 触发器是一类特定的事务, 可以监视某种数据操作(insert, update, delete) 并触发相关操作(insert, update, delete).

即监察增删改, 触发增删改, sql_01 ...... 触发-->sql_n

应用场景

  1. 当向一张表增删记录时, 需要在相关表中进行同步操作.(订单, 销量增加, 库存减少..)
  2. 当表上的某列数据值与其他表中的数据有联系时.(信用卡消费,额度校验)
  3. 跟踪某张表.(花名册更新)

创建语法4要素

  • 监视地点 table
  • 监视事件 insert, update, delete
  • 触发时间 afer, before
  • 触发事件 insert, update, delete

触发器-案例

需求:

商品表: goods

订单表: ord

当下1个订单时, 对应的商品库存减少

分析:

监视谁: ord

监视动作: insert

触发时间: after

触发事件: update

(当ord插入后, 触发update)

 
 
 
xxxxxxxxxx
 
 
 
 
-- triger 语法结构
create trigger t1
after
inset
on ord
for each row
begin
update goods .....
end;
​
 

 

 
 
 
x
 
 
 
 
-- 创建一个数据库 mysql_advance
create database sql_advance charset=utf8;
use sql_advance;
​
​
-- 创建表
create table goods(
    gid int,
    name varchar(20),
    num smallint
);
​
create table ord(
    oid int,
    gid int,
    much smallint
);
​
-- 插入数据-goods表
insert into goods values
(1, 'cat', 34),
(2, 'dog', 65),
(3, 'pig', 21);
​
-- 插入测试
select * from goods;
select * from ord;
​
-- 下订单 ord (insert)
-- 常规操作
insert into ord values (123, 1, 2);
update goods set num=num-2 where gid=1;
-- 查看goods表2类商品数量是否变化 34=>32 
select * from goods;
​
 

用触发器实现

 
 
 
 
 
 
 
 
drop trigger if exists t1;
delimiter //
create trigger t1
after
insert
on ord
for each row
begin
update goods set num=num-2 where gid=1;
end //
delimiter ;
​
-- test
show triggers \G;
 

\G 能显示详细, 但navicat不支持, 终端可以的 (直接复制终端的命令行, 代码块能自动格式美化)

 
 
 
xxxxxxxxxx
 
 
 
 
mysql> show triggers \G;
*************************** 1. row ***************************
             Trigger: t1
               Event: INSERT
               Table: ord
           Statement: begin
update goods set num=num-2 where gid=1;
end
              Timing: AFTER
             Created: 2019-10-01 23:14:55.08
            sql_mode:
             Definer: root@localhost
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)
​
ERROR:
No query specified
 
 
 
 
x
 
 
 
 
-- test
select * from goods;
select * from ord;
-- 下订单, 目前有32只, 现再买2只
insert into ord values (124, 1, 2);
-- 查看goods表是否也跟着变更了
select * from goods;
 

输出如下

 
 
 
xxxxxxxxxx
 
 
 
 
mysql> select * from goods;
+-----+------+-----+
| gid | name | num |
+-----+------+-----+
|   1 | cat  |  32 |
|   2 | dog  |  65 |
|   3 | pig  |  21 |
+-----+------+-----+
3 rows in set (0.08 sec)
​
mysql> select * from ord;
+-----+-----+------+
| oid | gid | much |
+-----+-----+------+
| 123 |   2 |    2 |
| 123 |   1 |    2 |
+-----+-----+------+
2 rows in set (0.07 sec)
​
mysql> insert into ord values (125, 1, 2);
Query OK, 1 row affected (0.09 sec)
​
mysql> select * from goods;
+-----+------+-----+
| gid | name | num |
+-----+------+-----+
|   1 | cat  |  30 |
|   2 | dog  |  65 |
|   3 | pig  |  21 |
+-----+------+-----+
3 rows in set (0.07 sec)
 

局限性: update goods set num=num-2 where gid=1; 应该设置为变量才灵活. 即insert(被监视的语句), 产生的数据能否在触发器中引用到?

触发器引用行变量

下订单: 关键词: NEW

insert 操作时, 看作是NEW一个新行, new关键字, 即取到该行(类似对象)

 
 
 
xxxxxxxxxx
 
 
 
 
drop trigger if exists t2;
delimiter //
create trigger t2
after
insert
on ord
for each row
begin
update goods set num=num-NEW.much where gid=NEW.gid;
end //
delimiter ;
​
-- 查看已有 triggers: show triggers;
-- 删除已有 triggers: drop trigger [if exists] triggerName
​
-- test
select * from goods;
select * from ord;
-- 分别去购买1,2,3号商品, 对应的goods表也会发生变化
insert into ord values (128, 1, 3);
insert into ord values (130, 2, 5);
insert into ord values (131, 3, 1)
-- 查看数量是否改变
select * from goods;
 

效果:

 
 
 
 
 
 
 
 
mysql> select * from goods;
select * from ord;
+-----+------+-----+
| gid | name | num |
+-----+------+-----+
|   1 | cat  |  26 |
|   2 | dog  |  65 |
|   3 | pig  |  21 |
+-----+------+-----+
3 rows in set (0.06 sec)
​
+-----+-----+------+
| oid | gid | much |
+-----+-----+------+
| 123 |   2 |    2 |
| 123 |   1 |    2 |
| 125 |   1 |    2 |
| 126 |   2 |    5 |
| 127 |   1 |   10 |
+-----+-----+------+
5 rows in set (0.08 sec)
​
mysql> insert into ord values (128, 1, 3);
Query OK, 1 row affected (0.07 sec)
​
mysql> select * from goods;
+-----+------+-----+
| gid | name | num |
+-----+------+-----+
|   1 | cat  |  23 |
|   2 | dog  |  65 |
|   3 | pig  |  21 |
+-----+------+-----+
3 rows in set (0.07 sec)
​
mysql> insert into ord values (130, 2, 5);
Query OK, 1 row affected (0.05 sec)
​
mysql> select * from goods;
+-----+------+-----+
| gid | name | num |
+-----+------+-----+
|   1 | cat  |  23 |
|   2 | dog  |  60 |
|   3 | pig  |  21 |
+-----+------+-----+
3 rows in set (0.08 sec)
​
 

删订单: 关键词: OLD , 引用delete的行

曾经的一行old

 
 
 
x
 
 
 
 
drop trigger if exists t3;
delimiter //
create trigger t3
after
delete 
on ord
for each row
begin
-- 删掉一条订单后, 对应的goods表应该对应增加该订单的数量
update goods set num=num+OLD.much where gid=OLD.gid;
end //
delimiter ;
 

效果如下

 
 
 
 
 
 
 
 
mysql> drop trigger if exists t3;
delimiter //
create trigger t3
after
delete 
on ord
for each row
begin
-- 删掉一条订单后, 对应的goods表应该对应增加该订单的数量
update goods set num=num+OLD.much where gid=OLD.gid;
end //
delimiter ;
Query OK, 0 rows affected (0.00 sec)
​
Query OK, 0 rows affected (0.11 sec)
​
mysql> select * from ord;
+-----+-----+------+
| oid | gid | much |
+-----+-----+------+
| 123 |   2 |    2 |
| 123 |   1 |    2 |
| 125 |   1 |    2 |
| 126 |   2 |    5 |
| 127 |   1 |   10 |
| 128 |   1 |    3 |
| 130 |   2 |    5 |
| 131 |   3 |    1 |
+-----+-----+------+
8 rows in set (0.09 sec)
​
mysql> select * from goods where gid=2;
+-----+------+-----+
| gid | name | num |
+-----+------+-----+
|   2 | dog  |  60 |
+-----+------+-----+
1 row in set (0.07 sec)
​
mysql> delete from ord where oid=123;
Query OK, 2 rows affected (0.10 sec)
​
mysql> select * from goods;
+-----+------+-----+
| gid | name | num |
+-----+------+-----+
|   1 | cat  |  25 |
|   2 | dog  |  62 |
|   3 | pig  |  20 |
+-----+------+-----+
3 rows in set (0.09 sec)
​
 

 

 
 
 
xxxxxxxxxx
 
 
 
 
drop trigger if exists t3;
delimiter //
create trigger t3
after
delete
on ord
for each row
begin
update goods set num=num+old.much where gid=old.gid
end //
delimiter ;
 

改订单 (数量) update : 结合old和new, 改之前是old, 改之后是new.

 
 
 
xxxxxxxxxx
 
 
 
 
delimiter //
create trigger t4
before
update
on ord
for each row
begin
update goods set num = num + old.much - new.much where gid = old.gid;
end //
delimiter ;
​
-- test
select * from goods;
select * from ord;
​
update ord set much=2 where gid=125;
 
 
 
 
x
 
 
 
 
-- 爆仓测试
delete from ord;
select * from goods;
-- 还剩21只pig, 现在买30只
insert into ord values (1, 3, 30);
select * from goods;
 

new & old

需求: 将30的数量改为10

原理: 先删掉30, 再加回10, 即先old, 再new.

 
 
 
x
 
 
 
 
mysql> select * from ord;
+-----+-----+------+
| oid | gid | much |
+-----+-----+------+
|   1 |   3 |   30 |
+-----+-----+------+
1 row in set (0.06 sec)
​
-- 在update之前(before)
drop trigger if exists t4;
delimiter //
create trigger t4
before 
update on ord
for each row
begin
update goods set num=num+old.much - new.much where gid=old.gid;
end //
delimiter ;
​
 
 
 
 
xxxxxxxxxx
 
 
 
 
--  爆仓演示
mysql> select * from goods;
+-----+------+-----+
| gid | name | num |
+-----+------+-----+
|   1 | cat  |  40 |
|   2 | dog  |  72 |
|   3 | pig  |  21 |
+-----+------+-----+
3 rows in set (0.05 sec)
​
mysql> insert into ord values (1, 3, 30);
Query OK, 1 row affected (0.05 sec)
​
mysql> select * from goods;
+-----+------+-----+
| gid | name | num |
+-----+------+-----+
|   1 | cat  |  40 |
|   2 | dog  |  72 |
|   3 | pig  |  -9 |
+-----+------+-----+
3 rows in set (0.07 sec)
 

过程:

 
 
 
xxxxxxxxxx
 
 
 
 
mysql> drop trigger if exists t4;
delimiter //
create trigger t4
before 
update on ord
for each row
begin
update goods set num=num+old.much - new.much where gid=old.gid;
end //
delimiter ;
Query OK, 0 rows affected (0.00 sec)
​
Query OK, 0 rows affected (0.12 sec)
​
mysql> select * from goods;
+-----+------+-----+
| gid | name | num |
+-----+------+-----+
|   1 | cat  |  40 |
|   2 | dog  |  72 |
|   3 | pig  |  -9 |
+-----+------+-----+
3 rows in set (0.05 sec)
​
mysql> select * from ord;
+-----+-----+------+
| oid | gid | much |
+-----+-----+------+
|   1 |   3 |   30 |
+-----+-----+------+
1 row in set (0.06 sec)
-- 即 -9 + 30 -10 = 11
mysql> update ord set much=10 where oid=1;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0
​
mysql> select * from goods;
+-----+------+-----+
| gid | name | num |
+-----+------+-----+
|   1 | cat  |  40 |
|   2 | dog  |  72 |
|   3 | pig  |  11 |
+-----+------+-----+
3 rows in set (0.07 sec)
 

Q1: before 与 afer 的而区别在哪?

Q2: 如何预防"爆仓"?

Q3: 在购买量 much > 库存量 num时, 把much自动改为num?

 
 
 
x
 
 
 
 
-- 在t2的基础上, 完成 much 与 num 的判断
drop trigger t5 if exists;
delimiter //
create trigger t5
after
inset
on ord
-- 声明变量用来存储查询到的剩余库存num值
declare rNum int;
for each row
begin
-- 查询到剩余库存
select num INTO rNum from goods where gid=NEW.gid;
-- if much > num 就爆仓了呀
if NEW.much > rNum 
​
​
update goods set num=num-NEW.much where gid=NEW.gid
end //
delimiter ;
 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

posted @ 2019-10-02 14:38  致于数据科学家的小陈  阅读(272)  评论(0)  编辑  收藏  举报