触发器（Trigger）

蓬祺

2023-12-01

触发器（Trigger）

触发器是一种特殊类型的存储过程，不由用户直接调用。创建触发器时会对其进行定义，以便在对特定表或列作特定类型的数据修改时执行。触发器可以查询其他表，而且可以包含复杂的 SQL 语句。它们主要用于强制服从复杂的业务规则或要求。例如，您可以根据客户当前的帐户状态，控制是否允许插入新订单。
触发器也可用于强制引用完整性，以便在多个表中添加、更新或删除行时，保留在这些表之间所定义的关系

优点

触发器可通过数据库中的相关表实现级联更改；通过级联引用完整性约束可以更有效地执行这些更改；
触发器可以强制比用 CHECK 约束定义的约束更为复杂的约束。与 CHECK 约束不同，触发器可以引用其它表中的列。例如，触发器可以使用另一个表中的 SELECT 比较插入或更新的数据，以及执行其它操作，如修改数据或显示用户定义错误信息；
触发器还可以强制执行业务规则；
触发器也可以评估数据修改前后的表状态，并根据其差异采取对策；

缺点

过多的触发器使得数据逻辑变得复杂；
数据操作比较隐含，不易进行调整修改；
触发器的功能逐渐在代码逻辑或事务中替代实现，更符合面向对象的思想。

基本操作

注意：本文的SQL 在 MySQL 8.0版本中测试通过

创建触发器

CREATE
	[DEFINER = {user | CURRENT_USER}]
	TRIGGER trigger_name
	trigger_time trigger_event
	ON Table_name FOR EACH ROW
	trigger_body

DEFINER：定义可以激活触发器的用户

trigger_time：值只有两个，即before和after，表示触发器执行的时间在事件发生前或发生后

trigger_event：值只有三个，即insert，update和delete，表示触发触发器的事件

ON Table_name：触发器是关联在那个表上的

FOR EACH ROW：表示触发器执行的间隔，这里表示每个修改的记录都会触发触发器

trigger_body：代表触发器所要触发的动作

查看触发器

show triggers;

删除触发器

drop trigger [if exists] trigger_name;

应用实例

创建如下商品表和订单表，并插入初始商品信息，其中订单的初始编号从1000开始。

create table orders(
	order_id INT AUTO_INCREMENT PRIMARY KEY,
    good_id INT,
    order_number INT
);
alter table orders AUTO_INCREMENT = 1000;
create table goods(
	id INT,
    name varchar(255),
    price FlOAT,
    number INT
);
insert into goods values(10001, "cream", 80, 1000);
insert into goods values(10002, "cleanser", 40, 5000);
insert into goods values(10003, "sweater", 99, 13000);
insert into goods values(10004, "honey", 88, 14000);
insert into goods values(10005, "keyboard", 66, 12000);

新增订单时，对订单进行限购处理，每次订单数量小于100，不符合要求时给出相应提示，此时可以创建插入触发器如下：

DELIMITER $;
create trigger order_insert 
	before insert on orders 
    for each row 
    begin
		if new.order_number <= 0 then
			signal sqlstate '40001' set message_text = 'the order number should more than 0';
            set new.order_number = 1;
		elseif new.order_number >= 100 then
			signal sqlstate '40002' set message_text = 'the order number should less than 100';
            set new.order_number = 99;
		else
			update goods set number = number - new.order_number where id = new.good_id;
        end if;
	end;

插入数据验证如下：

mysql> insert into orders(good_id, order_number) values(10001, 99);
Query OK, 1 row affected (0.01 sec)

mysql> select * from goods;
+-------+----------+-------+--------+
| id    | name     | price | number |
+-------+----------+-------+--------+
| 10001 | cream    |    80 |    901 |
| 10002 | cleanser |    40 |   5000 |
| 10003 | sweater  |    99 |  13000 |
| 10004 | honey    |    88 |  14000 |
| 10005 | keyboard |    66 |  12000 |
+-------+----------+-------+--------+
5 rows in set (0.00 sec)

mysql> select * from orders;
+----------+---------+--------------+
| order_id | good_id | order_number |
+----------+---------+--------------+
|     1000 |   10001 |           99 |
+----------+---------+--------------+
1 row in set (0.00 sec)

mysql> insert into orders(good_id, order_number) values(10001, -3);
ERROR 1644 (40001): the order number should more than 0
mysql> insert into orders(good_id, order_number) values(10001, 100);
ERROR 1644 (40002): the order number should less than 100

修改订单信息时，需要对商品信息进行同步处理，此时创建更新触发器如下：

DELIMITER $;
create trigger order_update 
	before update on orders 
    for each row 
    begin
		if new.order_number <= 0 then
			signal sqlstate '40003' set message_text = 'the order number should more than 0';
            set new.order_number = 1;
		elseif new.order_number >= 100 then
			signal sqlstate '40004' set message_text = 'the order number should less than 100';
            set new.order_number = 99;
		else
			update goods set number = number + old.order_number - new.order_number where id = new.good_id;
        end if;
	end;

更新数据验证如下：

mysql> update orders set order_number = 199 where order_id = 1000;
ERROR 1644 (40004): the order number should less than 100
mysql> update orders set order_number = -6 where order_id = 1000;
ERROR 1644 (40003): the order number should more than 0
mysql> update orders set order_number = 88 where order_id = 1000;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from goods;
+-------+----------+-------+--------+
| id    | name     | price | number |
+-------+----------+-------+--------+
| 10001 | cream    |    80 |    912 |
| 10002 | cleanser |    40 |   5000 |
| 10003 | sweater  |    99 |  13000 |
| 10004 | honey    |    88 |  14000 |
| 10005 | keyboard |    66 |  12000 |
+-------+----------+-------+--------+
5 rows in set (0.00 sec)

mysql> select * from orders;
+----------+---------+--------------+
| order_id | good_id | order_number |
+----------+---------+--------------+
|     1000 |   10001 |           88 |
+----------+---------+--------------+
1 row in set (0.00 sec)

当取消订单时，也需要对商品信息进行同步处理，此时创建删除触发器如下：

DELIMITER $;
create trigger order_delete 
	after delete on orders 
    for each row 
    begin
		update goods set number = number + old.order_number where id = old.good_id;
	end;

删除数据验证如下：

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

mysql> select * from goods;
+-------+----------+-------+--------+
| id    | name     | price | number |
+-------+----------+-------+--------+
| 10001 | cream    |    80 |   1000 |
| 10002 | cleanser |    40 |   5000 |
| 10003 | sweater  |    99 |  13000 |
| 10004 | honey    |    88 |  14000 |
| 10005 | keyboard |    66 |  12000 |
+-------+----------+-------+--------+
5 rows in set (0.00 sec)
mysql> select * from orders;
Empty set (0.00 sec)

触发器（Trigger）