深入讲解 MySQL数据库 触发器 trigger

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

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

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;

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 |
+—-+——–+——-+

— 删除原有触发器
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)