MySQL触发器（trigger）的创建

最新推荐文章于 2024-02-02 08:44:26 发布

睿思达DBA_WGX

最新推荐文章于 2024-02-02 08:44:26 发布

阅读量2.4k

点赞数 5

分类专栏： MySQL基本操作文章标签：数据库 MySQL 触发器

本文链接：https://blog.csdn.net/weixin_44377973/article/details/103219681

版权

MySQL基本操作专栏收录该内容

169 篇文章 93 订阅

订阅专栏

MySQL触发器（trigger）的创建

触发器（trigger）和存储过程类似，也是代码片段的集合，触发器是MySQL用来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由某个事件来触发。触发器与存储过程的唯一区别是触发器不能通过执行CALL命令来调用，而是在用户执行数据更新操作（update、insert、delete ）时自动触发执行。

触发器是一个与表相关的数据库对象，在满足某种条件时触发，以执行触发器中定义的语句。当使用update、insert、delete from命令对表中的数据进行操作时都可能会激活触发器。

一、创建触发器的语法

创建触发器基本语法如下：

create trigger triggerName 
after/before insert/update/delete on tablename
for each row
begin
    sql-statement;
end;

说明：

1、triggerName：定义触发器的名称；
2、after/before：触发时间，after是在记录操纵之后触发，是先完成数据的增删改，再触发。before是在记录操纵之前触发，是先完成触发，再增删改。通过before我们可以修改即将发生的操作，比如在触发之前判断new值和old值的大小或关系，如果满足要求就触发，不通过就修改后再触发；
3、insert/update/delete：触发事件，即定义哪种操作会执行触发器，对同一个表相同触发时间的相同触发事件，只能定义一个触发器；
4、tablename：操作哪个表时会执行触发器；
5、for each row：固定写法；
6、可以使用old和new来引用触发器中发生变化记录的内容，其中new.colum_name表示某个字段变化之后的记录内容，old.colum_name表示某个字段表示变化之前的记录内容。对于insert触发器，不能使用old（插入之前没有数据），可以使用new；对于delete，不能使用new，可以使用old；对于update，既可以使用old，也可以使用new。其中new和old相当于一个对象，封装了当前操作数据的所有字段。

触发器的创建可以归纳为四要素：即（1）监视地点(tablename)；（2）监视事件(insert/update/delete) ；（3）触发时间(after/before) ；（4）触发事件(insert/update/delete)。

二、数据准备

创建两张表：商品（goods）和订单（orders），表结构如下：

create table goods(
    goods_id int primary key auto_increment comment '商品编号',
    goods_name varchar(50) not null default '' comment '商品名称',
    model char(20) not null default '' comment '型号',
    specifications char(20) not null default '' comment '规格',
    unit char(20) not null default '' comment '单位',
    inventory_number decimal(10,2) not null default 0 comment '库存数量'
);

create table orders(
    orders_id int primary key auto_increment comment '订单编号',
    goods_id int not null default 0 comment '商品编号',
    number decimal(10,2) not null default 0 comment '订购数量'
);

为goods表插入一下数据：

insert into goods(goods_name,model,specifications,unit,inventory_number)
values('移动硬盘','S001-500','500GB','个',20),('移动硬盘','S001-1000','1TB','个',15),
('笔记本','HPSV001-C','I7-16GB-1TB','台',5),('台式机','LX-S-V1','I5-4GB','台',6),
('U盘','KS-16','16GB','个',200),('U盘','KS-32','32GB','个',200),
('U盘','KS-64','64GB','个',200);

mysql> select * from goods;
+----------+--------------+-----------+----------------+------+------------------+
| goods_id | goods_name   | model     | specifications | unit | inventory_number |
+----------+--------------+-----------+----------------+------+------------------+
|        1 | 移动硬盘     | S001-500  | 500GB          | 个   |            20.00 |
|        2 | 移动硬盘     | S001-1000 | 1TB             | 个   |            15.00 |
|        3 | 笔记本       | HPSV001-C | I7-16GB-1TB    | 台   |             5.00 |
|        4 | 台式机       | LX-S-V1   | I5-4GB         | 台   |             6.00 |
|        5 | U盘          | KS-16     | 16GB           | 个   |           200.00 |
|        6 | U盘          | KS-32     | 32GB           | 个   |           200.00 |
|        7 | U盘          | KS-64     | 64GB           | 个   |           200.00 |
+----------+--------------+-----------+----------------+------+------------------+
7 rows in set (0.00 sec)

三、创建after类型的触发器

after类型的触发器是定义insert、update、delete事件触发之后执行的操作。

（一）针对订单（orders）表创建after insert触发器，当生成订单时自动减少所订购商品的库存量

delimiter //

create trigger trigger_after_insert_orders
after insert on orders 
for each row
begin
    update goods set inventory_number=inventory_number-new.number
    where goods_id=new.goods_id;
end //

delimiter ;

此触发器使用new关键字取出新订单订购的商品编号和订购数量。用【new.goods_id】从goods表查询商品，用【new.number】修改商品的库存量。
生成如下订单，查看goods表的数据变动情况：

mysql> insert into orders(goods_id,number) values(5,6);
Query OK, 1 row affected (0.03 sec)

mysql> select * from orders;
+-----------+----------+--------+
| orders_id | goods_id | number |
+-----------+----------+--------+
|         1 |        5 |   6.00 |
+-----------+----------+--------+
1 row in set (0.00 sec)

mysql> select * from goods;
+----------+--------------+-----------+----------------+------+------------------+
| goods_id | goods_name   | model     | specifications | unit | inventory_number |
+----------+--------------+-----------+----------------+------+------------------+
|        1 | 移动硬盘     | S001-500  | 500GB          | 个   |            20.00 |
|        2 | 移动硬盘     | S001-1000 | 1TB             | 个   |            15.00 |
|        3 | 笔记本       | HPSV001-C | I7-16GB-1TB    | 台   |             5.00 |
|        4 | 台式机       | LX-S-V1   | I5-4GB         | 台   |             6.00 |
|        5 | U盘          | KS-16     | 16GB           | 个   |           194.00 |
|        6 | U盘          | KS-32     | 32GB           | 个   |           200.00 |
|        7 | U盘          | KS-64     | 64GB           | 个   |           200.00 |
+----------+--------------+-----------+----------------+------+------------------+
7 rows in set (0.00 sec)

可以看出，当生成订单之后，5号商品的库存减少了6个，由200变成了194，然后再生成如下几个订单并查看结果：

mysql> insert into orders(goods_id,number) values(1,1);
Query OK, 1 row affected (0.03 sec)

mysql> insert into orders(goods_id,number) values(2,3);
Query OK, 1 row affected (0.02 sec)

mysql> insert into orders(goods_id,number) values(6,4);
Query OK, 1 row affected (0.01 sec)

mysql> insert into orders(goods_id,number) values(7,30);
Query OK, 1 row affected (0.01 sec)

mysql> select * from orders;
+-----------+----------+--------+
| orders_id | goods_id | number |
+-----------+----------+--------+
|         1 |        5 |   6.00 |
|         2 |        1 |   1.00 |
|         3 |        2 |   3.00 |
|         4 |        6 |   4.00 |
|         5 |        7 |  30.00 |
+-----------+----------+--------+
5 rows in set (0.00 sec)

mysql> select * from goods;
+----------+--------------+-----------+----------------+------+------------------+
| goods_id | goods_name   | model     | specifications | unit | inventory_number |
+----------+--------------+-----------+----------------+------+------------------+
|        1 | 移动硬盘     | S001-500  | 500GB          | 个   |            19.00 |
|        2 | 移动硬盘     | S001-1000 | 1TB             | 个   |            12.00 |
|        3 | 笔记本       | HPSV001-C | I7-16GB-1TB    | 台   |             5.00 |
|        4 | 台式机       | LX-S-V1   | I5-4GB         | 台   |             6.00 |
|        5 | U盘          | KS-16     | 16GB           | 个   |           194.00 |
|        6 | U盘          | KS-32     | 32GB           | 个   |           196.00 |
|        7 | U盘          | KS-64     | 64GB           | 个   |           170.00 |
+----------+--------------+-----------+----------------+------+------------------+
7 rows in set (0.00 sec)

（二）针对订单（orders）表创建after delete触发器，当删除（撤销）订单时自动增加商品的库存量

delimiter //

create trigger trigger_after_delete_orders
after delete on orders 
for each row
begin
    update goods set inventory_number=inventory_number+old.number
    where goods_id=old.goods_id;
end //

delimiter ;

该触发器使用old关键字取出要删除订单订购的商品编号和订购数量。用【old.goods_id】从goods表查询商品，用【old.number】修改商品的库存量。
删除（撤销）5号订单，查看goods表的数据变动情况：

mysql> delete from orders where orders_id=5;
Query OK, 1 row affected (0.01 sec)

mysql> select * from orders;
+-----------+----------+--------+
| orders_id | goods_id | number |
+-----------+----------+--------+
|         1 |        5 |   6.00 |
|         2 |        1 |   1.00 |
|         3 |        2 |   3.00 |
|         4 |        6 |   4.00 |
+-----------+----------+--------+
4 rows in set (0.00 sec)

mysql> select * from goods;
+----------+--------------+-----------+----------------+------+------------------+
| goods_id | goods_name   | model     | specifications | unit | inventory_number |
+----------+--------------+-----------+----------------+------+------------------+
|        1 | 移动硬盘     | S001-500  | 500GB          | 个   |            19.00 |
|        2 | 移动硬盘     | S001-1000 | 1TB             | 个   |            12.00 |
|        3 | 笔记本       | HPSV001-C | I7-16GB-1TB    | 台   |             5.00 |
|        4 | 台式机       | LX-S-V1   | I5-4GB         | 台   |             6.00 |
|        5 | U盘          | KS-16     | 16GB           | 个   |           194.00 |
|        6 | U盘          | KS-32     | 32GB           | 个   |           196.00 |
|        7 | U盘          | KS-64     | 64GB           | 个   |           200.00 |
+----------+--------------+-----------+----------------+------+------------------+
7 rows in set (0.00 sec)

（三）针对订单（orders）表创建after update触发器，当修改订单的订购量时自动更新商品的库存量

delimiter //

create trigger trigger_after_update_orders
after update on orders 
for each row
begin
    update goods set inventory_number=inventory_number+old.number-new.number
    where goods_id=old.goods_id;
end //

delimiter ;

该触发器使用【old.goods_id】取出订单中订购的商品编号，用【old.number】取出订单修改前的订购量，用【new.number】取出订单修改后的订购量，并对商品的库存量进行更新。
把1号订单（订购的是5号商品）的订购量由6个修改为2个，把3号订单（订购的是2号商品）的订购量由3个修改为5个，查看goods表的数据变动情况：

mysql> update orders set number=2 where orders_id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update orders set number=5 where orders_id=3;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from orders;
+-----------+----------+--------+
| orders_id | goods_id | number |
+-----------+----------+--------+
|         1 |        5 |   2.00 |
|         2 |        1 |   1.00 |
|         3 |        2 |   5.00 |
|         4 |        6 |   4.00 |
+-----------+----------+--------+
4 rows in set (0.00 sec)

mysql> select * from goods;
+----------+--------------+-----------+----------------+------+------------------+
| goods_id | goods_name   | model     | specifications | unit | inventory_number |
+----------+--------------+-----------+----------------+------+------------------+
|        1 | 移动硬盘     | S001-500  | 500GB          | 个   |            19.00 |
|        2 | 移动硬盘     | S001-1000 | 1TB             | 个   |            10.00 |
|        3 | 笔记本       | HPSV001-C | I7-16GB-1TB    | 台   |             5.00 |
|        4 | 台式机       | LX-S-V1   | I5-4GB         | 台   |             6.00 |
|        5 | U盘          | KS-16     | 16GB           | 个   |           198.00 |
|        6 | U盘          | KS-32     | 32GB           | 个   |           196.00 |
|        7 | U盘          | KS-64     | 64GB           | 个   |           200.00 |
+----------+--------------+-----------+----------------+------+------------------+
7 rows in set (0.00 sec)

四、before类型的触发器

针对订单（orders）表创建before insert触发器，在生成新订单之前检查所订购商品的库存量。首先删除orders表的after insert触发器，然后再创建before insert触发器。

drop trigger trigger_after_insert_orders;   --删除orders表的after insert触发器

delimiter //

drop trigger if exists trigger_before_insert_orders;
create trigger trigger_before_insert_orders
before insert on orders 
for each row
begin
    declare goods_num decimal(10,2);
    declare msg varchar(200);
    select inventory_number into goods_num from goods where goods_id=new.goods_id;
    select concat(goods_name,'商品库存不足，请重新下订单！') into msg from goods where goods_id=new.goods_id;
    if (goods_num >= new.number) then
    	update goods set inventory_number=inventory_number-new.number
    	where goods_id=new.goods_id;
    else
	    signal sqlstate '45000' set message_text=msg;
    end if;
end //

delimiter ;


mysql> insert into orders(goods_id,number) values(3,10);
ERROR 1644 (45000): 笔记本商品库存不足，请重新下订单！
mysql> insert into orders(goods_id,number) values(5,35);
Query OK, 1 row affected (0.01 sec)

mysql> select * from orders;
+-----------+----------+--------+
| orders_id | goods_id | number |
+-----------+----------+--------+
|         1 |        5 |   2.00 |
|         2 |        1 |   1.00 |
|         3 |        2 |   5.00 |
|         4 |        6 |   4.00 |
|         5 |        7 |  20.00 |
|         6 |        5 |  35.00 |
+-----------+----------+--------+
8 rows in set (0.00 sec)

mysql> select * from goods;
+----------+--------------+-----------+----------------+------+------------------+
| goods_id | goods_name   | model     | specifications | unit | inventory_number |
+----------+--------------+-----------+----------------+------+------------------+
|        1 | 移动硬盘     | S001-500  | 500GB          | 个   |            19.00 |
|        2 | 移动硬盘     | S001-1000 | 1TB             | 个   |            10.00 |
|        3 | 笔记本       | HPSV001-C | I7-16GB-1TB    | 台   |             5.00 |
|        4 | 台式机       | LX-S-V1   | I5-4GB         | 台   |             6.00 |
|        5 | U盘          | KS-16     | 16GB           | 个   |           163.00 |
|        6 | U盘          | KS-32     | 32GB           | 个   |           196.00 |
|        7 | U盘          | KS-64     | 64GB           | 个   |           180.00 |
+----------+--------------+-----------+----------------+------+------------------+
7 rows in set (0.00 sec)

从程序的运行结果可以看出，当生成订单时，如果订购的商品数量超过库存量，则不会插入该订单的信息。

睿思达DBA_WGX

关注

5
点赞
踩
25

收藏

觉得还不错? 一键收藏
打赏
2
评论
MySQL触发器（trigger）的创建

MySQL中的触发器（trigger）触发器（trigger）和存储过程类似，也是代码片段的集合，触发器是MySQL用来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由某个事件来触发。触发器与存储过程的唯一区别是触发器不能通过执行CALL命令来调用，而是在用户执行数据更新操作（update、insert、delete ）时自动触发执行。...
复制链接

扫一扫