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

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 ;
相关阅读:
Python网络爬虫与信息提取（一）——Requests库的安装、Requests库的get()方法、爬取网页的通用代码框架
 CodeForces#632Div.2C. Eugene and an array
CodeForces#631Div.2B_Dreamoon Likes Permutations
hdu6228Tree
hdu6576Worker(最小公倍数)
hdu6575Budget
hdu6570Wave (暴力求解)
poj2010 Moo University
poj1258Agri-Net（最小生成树）
poj2377Bad Cowtractors （最小生成树变形之——最大生成树）
原文地址：https://www.cnblogs.com/chenjieyouge/p/11617412.html

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

触发器(Trigger)

目标

定义

应用场景

创建语法4要素

触发器-案例

用触发器实现

触发器引用行变量