深入讲解 MySQL数据库触发器 trigger - 主机乐

首页

超值活动

产品中心

物理机租用

十堰物理机官方自营机柜。全系列戴尔物理机、优质设备、专业售后团队，稳定在线

物理机租用

襄阳物理机官方自营机柜。全系列戴尔物理机、优质设备、专业售后团队，稳定在线

物理机租用

宁波物理机官方自营机柜。全系列戴尔物理机、优质设备、专业售后团队，稳定在线

云服务器

湖北-十堰高防电信云服务器-200G防御，官方自营机柜，全系铂金8163宿主机/DDR4/U2固态 SAS备份盘

云服务器

陕西-西安西安电信云服务器-100G防御，官方自营机柜，宿主机金牌5218/DDR4/U2固态

云服务器

美国-圣何塞官方自营机柜。全系列戴尔物理机、优质设备、专业售后团队，稳定在线

云服务器

香港-将军澳官方自营机柜。全系列戴尔物理机、优质设备、专业售后团队，稳定在线

虚拟主机

美国主机官方自营机柜。全系列戴尔物理机、优质设备、专业售后团队，稳定在线

虚拟主机

陕西西安西安电信云服务器-100G防御，官方自营机柜，宿主机金牌5218/DDR4/U2固态

免费空间

香港为学习者提供稳定平台

免费空间

大陆为学习者提供稳定可用的平台

云电脑/挂机宝

贵州官方自营机柜。全系列戴尔物理机、优质设备、专业售后团队，稳定在线

订购产品

文章公告

关于我们

博客动态

网站首页

超值活动

所有文章

< 返回文章列表

【服务器相关】深入讲解 MySQL数据库触发器 trigger

发表时间：2025-06-16 03:46:00 小编：主机乐-Yutio

一、基本概念

触发器是一种特殊类型的存储过程，触发器通过事件进行触发而被执行

触发器 trigger 和js事件类似

1、作用

写入数据表前，强制检验或转换数据（保证数据安全）
触发器发生错误时，异动的结果会被撤销（事务安全）
部分数据库管理系统可以针对数据定义语言DDL使用触发器，称为DDL触发器
可以依照特定的情况，替换异动的指令 instead of（mysql不支持）

2、触发器的优缺点

2.1、优点

触发器可通过数据库中的相关表实现级联更改（如果一张表的数据改变，可以利用触发器实现对其他表的操作，用户不知道）
保证数据安全，进行安全校验

2.2、缺点

对触发器过分依赖，势必影响数据库的结构，同时增加了维护的复杂度
造成数据在程序层面不可控

二、创建触发器

1、基本语法

create trigger 触发器名字触发时机触发事件 on 表 for each row
begin
end

2、触发对象

on 表 for each row 触发器绑定表中所有行，没一行发生指定改变的时候，就会触发触发器

3、触发时机

每张表对应的行都有不同的状态，当SQL指令发生的时候，都会令行中数据发生改变，每一行总会有两种状态：数据操作前和数据操作后

before: 数据发生改变前的状态
after: 数据已经发生改变后的状态

4、触发事件

mysql中触发器针对的目标是数据发生改变，对应的操作只有写操作（增删改）

inert 插入操作
update 更新操作
delete 删除操作

5、注意事项

一张表中，每一个触发时机绑定的触发事件对应的触发器类型只能有一个

一张表表中只能有一个对应的after insert 触发器

最多只能有6个触发器

before insert
after insert
before update
after update
before delete
after delete

需求：

下单减库存

有两张表，一张是商品表，一张是订单表（保留商品ID）每次订单生成，商品表中对应的库存就应该发生变化

创建两张表：

create table my_item(
id int primary key auto_increment,
name varchar(20) not null,
count int not null default 0
) comment ‘商品表’;

create table my_order(
id int primary key auto_increment,
item_id int not null,
count int not null default 1
) comment ‘订单表’;

insert my_item (name, count) values (‘手机’, 100),(‘电脑’, 100), (‘包包’, 100);

mysql> select * from my_item;
+—-+——–+——-+
| id | name | count |
+—-+——–+——-+
|1 | 手机 | 100 |
|2 | 电脑 | 100 |
|3 | 包包 | 100 |
+—-+——–+——-+
3 rows in set (0.00 sec)

mysql> select * from my_order;
Empty set (0.02 sec)

创建触发器：

如果订单表发生数据插入，对应的商品就应该减少库存

delimiter $$
create trigger after_insert_order_trigger after insert on my_order for each row
begin
— 更新商品库存
update my_item set count = count – 1 where id = 1;
end
$$
delimiter ;

三、查看触发器

— 查看所有触发器
show triggers\G
*************************** 1. row ***************************
Trigger: after_insert_order_trigger
Event: INSERT
Table: my_order
Statement: begin

update my_item set count = count – 1 where id = 1;
end
Timing: AFTER
Created: 2022-04-16 10:00:19.09
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_general_ci
Database Collation: utf8mb4_general_ci
1 row in set (0.00 sec)
— 查看创建语句
show crate trigger 触发器名字;
— eg:
show create trigger after_insert_order_trigger;

四、触发触发器

让触发器执行，让触发器指定的表中，对应的时机发生对应的操作

insert into my_order (item_id, count) values(1, 1);

mysql> select * from my_order;
+—-+———+——-+
| id | item_id | count |
+—-+———+——-+
|1 | 1 | 1 |
+—-+———+——-+
1 row in set (0.00 sec)

mysql> select * from my_item;
+—-+——–+——-+
| id | name | count |
+—-+——–+——-+
|1 | 手机 |99 |
|2 | 电脑 | 100 |
|3 | 包包 | 100 |
+—-+——–+——-+
3 rows in set (0.00 sec)

五、删除触发器

drop trigger 触发器名字;
— eg
drop trigger after_insert_order_trigger;

六、触发器的应用

记录关键字 new old

1、完善

商品自动扣除库存

触发器针对的是数据表中的每条记录，每行数据再操作前后都有一个对应的状态

触发器在执行之前就将对应的数据状态获取到了：

将没有操作之前的数据状态都保存到old关键字中
操作后的状态都放在new中

触发器中，可以通过old和new来获取绑定表中对应的记录数据

基本语法：

关键字.字段名

old和new并不是所有触发器都有

insert 插入前为空，没有old
delete 清除数据，没有new

商品自动扣减库存:

delimiter $$
create trigger after_insert_order_trigger after insert on my_order for each row
begin
— 通过new关键字获取新数据的id 和数量
update my_item set count = count – new.count where id = new.item_id;
end
$$
delimiter ;

触发触发器:

mysql> select * from my_order;
+—-+———+——-+
| id | item_id | count |
+—-+———+——-+
|1 | 1 | 1 |
+—-+———+——-+
mysql> select * from my_item;
+—-+——–+——-+
| id | name | count |
+—-+——–+——-+
|1 | 手机 |99 |
|2 | 电脑 | 100 |
|3 | 包包 | 100 |
+—-+——–+——-+
insert into my_order (item_id, count) values(2, 3);
mysql> select * from my_order;
+—-+———+——-+
| id | item_id | count |
+—-+———+——-+
|1 | 1 | 1 |
|2 | 2 | 3 |
+—-+———+——-+
mysql> select * from my_item;
+—-+——–+——-+
| id | name | count |
+—-+——–+——-+
|1 | 手机 |99 |
|2 | 电脑 |97 |
|3 | 包包 | 100 |
+—-+——–+——-+

2、优化

如果库存数量没有商品订单多怎么办？

— 删除原有触发器
drop trigger after_insert_order_trigger;
— 新增判断库存触发器
delimiter $$
create trigger after_insert_order_trigger after insert on my_order for each row
begin
— 查询库存
select count from my_item where id = new.item_id into @count;

— 判断
if new.count > @count then
— 中断操作，暴力抛出异常
insert into xxx values (‘xxx’);

end if;

— 通过new关键字获取新数据的id 和数量
update my_item set count = count – new.count where id = new.item_id;
end
$$
delimiter ;

结果验证：

mysql> insert into my_order (item_id, count) values(3, 101);
ERROR 1146 (42S02): Table ‘mydatabase2.xxx’ doesn’t exist
mysql> select * from my_order;
+—-+———+——-+
| id | item_id | count |
+—-+———+——-+
|1 | 1 | 1 |
|2 | 2 | 3 |
+—-+———+——-+
2 rows in set (0.00 sec)

mysql> select * from my_item;
+—-+——–+——-+
| id | name | count |
+—-+——–+——-+
|1 | 手机 |99 |
|2 | 电脑 |97 |
|3 | 包包 | 100 |
+—-+——–+——-+
3 rows in set (0.00 sec)

到此这篇关于MySQL数据库触发器 trigger的文章就介绍到这了,更多相关MySQL trigger内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持！