本文介绍了MySQL触发器的概念及作用，详细讲解了如何创建单条和多条执行语句的触发器，并提供了查看和删除触发器的方法。通过示例展示了触发器在数据更新、插入时的自动操作，强调了使用触发器时的注意事项，如每个表每个事件只能有一个触发器。
12 _ MySQL触发器
触发器（trigger）是一个特殊的存储过程，不同的是，执行存储过程要使用CALL语句来调用，而触发器的执行不需要使用CALL语句来调用，也不需要手工启动，只要当一个预定义的事件发生的时候，就会被MySQL自动调用。
触发器可以查询其他表，而且可以包含复杂的SQL语句。它们主要用于满足复杂的业务规则或要求。例如，可以根据客户当前的账户状态控制是否允许插入新订单。本节将介绍如何创建触发器。
01 _ 创建触发器
CREATE TRIGGER `trigger_name` `trigger_time` `trigger_event`
ON `tbl_name` FOR EACH ROW `trigger_stmt`
------------------------------------------------------
`trigger_name` -- 表示触发器名称，用户自行指定
`trigger_time` -- 表示触发时机，可以指定为before或after
`trigger_event` -- 表示触发事件，包括INSERT、UPDATE和DELETE
`tbl_name` -- 表示建立触发器的表名，即在哪张表上建立触发器
`trigger_stmt` -- 是触发器执行语句，可以是一条语句。
-- 也可以用BEGIN和END作为开始和结束，中间包含多条语句。
创建一个单执行语句的触发器
- 创建一个account表，表中有两个字段，分别为acct_num字段（定义为int类型）和amount字段（定义成浮点类型）
CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
- 创建一个名为ins_sum的触发器，触发的条件是向数据表account插入数据之前，对新插入的amount字段值进行求和计算。
Create Trigger ins_sum Before Insert
On myaccount For Each Row Set @sum = @sum + NEW.amount;
- 插入数据，测试。
mysql> Set @sum = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> Insert Into account values(1,1.00),(2,2.00);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> Select @sum;
+------+
| @sum |
+------+
| 3.00 |
+------+
1 row in set (0.00 sec)
mysql>
创建有多个执行语句的触发器
- 创建几个表
Create Table test1(a1 Int);
Create Table test2(a2 Int);
Create Table test3(a3 Int Not Null Auto_Increment Primary Key);
Create Table test4(
a4 Int Not Null Auto_Increment Primary Key,
b4 Int Default 0
);
- 创建存储过程
Delimiter //
Create Trigger testref Before Insert On test1 For Each Row
Begin
Insert Into test2 Set a2 = NEW.a1;
Delete From test3 Where a3 = NEW.a1;
Update test4 Set b4 = b4 + 1 Where a4 = NEW.a1;
End;
Delimiter ;
- 插入，测试。
Insert Into test3 (a3) Values
(NULL),(NULL),(NULL),(NULL),(NULL),
(NULL),(NULL),(NULL),(NULL),(NULL);
Insert Into test4 (a4) Values
(0),(0),(0),(0),(0),(0),(0),(0),(0),(0);
--------------------------------------------
mysql> Insert Into test1 Values(1),(3),(1),(7),(1),(8),(4),(4);
Query OK, 8 rows affected (0.13 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql>
- 查看表数据
mysql> Select * From test1;
+------+
| a1 |
+------+
| 1 |
| 3 |
| 1 |
| 7 |
| 1 |
| 8 |
| 4 |
| 4 |
+------+
8 rows in set (0.00 sec)
mysql> Select * From test2;
+------+
| a2 |
+------+
| 1 |
| 3 |
| 1 |
| 7 |
| 1 |
| 8 |
| 4 |
| 4 |
+------+
8 rows in set (0.01 sec)
mysql> Select * From test3;
+----+
| a3 |
+----+
| 2 |
| 5 |
| 6 |
| 9 |
| 10 |
+----+
5 rows in set (0.00 sec)
mysql> Select * From test4;
+----+------+
| a4 | b4 |
+----+------+
| 1 | 3 |
| 2 | 0 |
| 3 | 1 |
| 4 | 2 |
| 5 | 0 |
| 6 | 0 |
| 7 | 1 |
| 8 | 1 |
| 9 | 0 |
| 10 | 0 |
+----+------+
10 rows in set (0.00 sec)
mysql>
INSERT触发了触发器，向test2中插入了test1中的值，删除了test3中相同的内容，同时更新了test4中的b4，即与插入的值相同的个数。
02 _ 查看触发器
查看触发器是指查看数据库中已存在的触发器的定义、状态和语法信息等。可以通过 SHOW TRIGGERS 和在 triggers表 中查看已经创建的触发器。
SHOW TRIGGERS
Show Triggers;
SHOW TRIGGERS 语句查看当前创建的所有触发器信息，在触发器较少的情况下，使用该语句会很方便。如果要查看特定触发器的信息，可以直接从 information_schema数据库 中的 triggers表 中查找。
栗子：
mysql> Show Triggers\G
*************************** 1. row ***************************
Trigger: ins_sum
Event: INSERT
Table: account
Statement: Set @sum = @sum + NEW.amount
Timing: BEFORE
Created: 2021-08-07 17:03:15.76
sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
Definer: root@localhost
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
Database Collation: utf8_general_ci
*************************** 2. row ***************************
Trigger: testref
Event: INSERT
Table: test1
Statement: Begin
Insert Into test2 Set a2 = NEW.a1;
Delete From test3 Where a3 = NEW.a1;
Update test4 Set b4 = b4 + 1 Where a4 = NEW.a1;
End
Timing: BEFORE
Created: 2021-08-07 17:37:11.73
sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
Definer: root@localhost
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
Database Collation: utf8_general_ci
2 rows in set (0.00 sec)
查 triggers 表
在MySQL中，所有触发器的定义都存在Information_Schema数据库的 Triggers 表格中，可以通过查询命令SELECT查看
Select * From information_schema.`TRIGGERS` Where condition;
栗子：
mysql> Select * From information_schema.`TRIGGERS`
> Where TRIGGERS.TRIGGER_NAME = 'testref'\G
*************************** 1. row ***************************
TRIGGER_CATALOG: def
TRIGGER_SCHEMA: mysql80_demo12
TRIGGER_NAME: testref
EVENT_MANIPULATION: INSERT
EVENT_OBJECT_CATALOG: def
EVENT_OBJECT_SCHEMA: mysql80_demo12
EVENT_OBJECT_TABLE: test1
ACTION_ORDER: 1
ACTION_CONDITION: NULL
ACTION_STATEMENT: Begin
Insert Into test2 Set a2 = NEW.a1;
Delete From test3 Where a3 = NEW.a1;
Update test4 Set b4 = b4 + 1 Where a4 = NEW.a1;
End
ACTION_ORIENTATION: ROW
ACTION_TIMING: BEFORE
ACTION_REFERENCE_OLD_TABLE: NULL
ACTION_REFERENCE_NEW_TABLE: NULL
ACTION_REFERENCE_OLD_ROW: OLD
ACTION_REFERENCE_NEW_ROW: NEW
CREATED: 2021-08-07 17:37:11.73
SQL_MODE: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
DEFINER: root@localhost
CHARACTER_SET_CLIENT: utf8mb4
COLLATION_CONNECTION: utf8mb4_0900_ai_ci
DATABASE_COLLATION: utf8_general_ci
1 row in set (0.00 sec)
mysql>
03 _ 触发器的使用
触发程序是与表有关的命名数据库对象，当表上出现特定事件时，将激活该对象。在某些触发程序的用法中，可用于检查插入到表中的值，或对更新涉及的值进行计算。
触发程序与表相关，当对表执行INSERT、DELETE或UPDATE语句时，将激活触发程序。可以将触发程序设置为在执行语句之前或之后激活。
栗子：创建一个在 account表 插入记录之后更新myevent数据表的触发器。
- account 表 与 myevent 表
Create Table account (acct_num INT, amount DECIMAL(10,2));
Create Table myevent (id Int, evt_name VarChar(20));
- 创建 trig_insert 触发器
Create Trigger trig_insert After Insert On account
For Each Row Insert Into myevent Values(2,'after insert');
- 给表 account 插入数据，查看表 myevent。
mysql> Insert Into account values(3,3.00),(4,4.00),(5,5.00);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> Select * From myevent;
+------+--------------+
| id | evt_name |
+------+--------------+
| 2 | after insert |
| 2 | after insert |
| 2 | after insert |
+------+--------------+
3 rows in set (0.00 sec)
mysql>
04 _ 删除触发器
DROP TRIGGER [schema_name.]trigger_name
----------------------------------------
`schema_name` 表示数据库名称，是可选的。如果省略了schema，将从当前数据库中舍弃触发程序
`trigger_name` 要删除的触发器的名称
05 _ 使用触发器的注意事项
-
对于相同的表，相同的事件只能创建一个触发器。
即，表 a 如果创建了一个
Before Insert触发器，那么再创建Before Insert便会报错。此时只能创建AFTER INSERT或者[Before | After] [Update | Delete]触发器。 -
需求变化时，及时删除不再需要的触发器，以免旧的触发器影响新的数据完整性。
扫一扫
2870
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
