触发器（Trigger）

骑猪看日落 2021-09-25 14:26 455阅读 0赞

## 触发器（Trigger） ##

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

### 优点 ###

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

### 缺点 ###

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

### 基本操作 ###

**注意：本文的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)