MySQL 8.0——触发器

MySQL的触发器和存储过程一样，都是嵌入到MySQL的一段程序。触发器是由事件来触发某个操作，这些事件包括INSERT、UPDATAE和DELETE语句。如果定义了触发程序，当数据库执行这些语句的时候就会激发触发器执行相应的操作，触发程序是与表有关的命名数据库对象，当表上出现特定事件时，将激活该对象。

1、创建触发器

触发器（trigger）是一个特殊的存储过程，不同的是，执行存储过程要使用CALL语句来调用，而触发器的执行不需要使用CALL语句来调用，也不需要手工启动，只要当一个预定义的事件发生的时候，就会被MySQL自动调用。比如当对fruits表进行操作（INSERT、DELETE或UPDATE）时就会激活它执行。

触发器可以查询其他表，而且可以包含复杂的SQL语句。它们主要用于满足复杂的业务规则或要求。例如，可以根据客户当前的账户状态控制是否允许插入新订单。

1.1、创建只有一个执行语句的触发器

创建一个触发器的语法如下：

CREATETRIGGERtrigger_name trigger_time trigger_eventONtbl_nameFOR EACH ROWtrigger_stmt

• trigger_name表示触发器名称，用户自行指定；
• trigger_time表示触发时机，可以指定为before或after；
• trigger_event表示触发事件，包括INSERT、UPDATE和DELETE；
• tbl_name表示建立触发器的表名，即在哪张表上建立触发器；
• trigger_stmt是触发器执行语句。

创建一个单执行语句的触发器，代码如下：

createtableaccount(acct_numint,amountdecimal(10,2));createtriggerins_sum beforeinsertonaccountfor each rowset@sum=@sum+NEW.amount;

首先，创建一个account表，表中有两个字段，分别为acct_num字段（定义为int类型）和amount字段（定义成浮点类型）​；其次，创建一个名为ins_sum的触发器，触发的条件是向数据表account插入数据之前，对新插入的amount字段值进行求和计算。

set@sum=0;insertintoaccountvalues(1,1.00),(2,2.00);select*fromaccount;+----------+--------+|acct_num|amount|+----------+--------+|1|1.00||2|2.00|+----------+--------+select@sum;+------+|@sum|+------+|3.00|+------+

首先，创建一个account表，在向表account插入数据之前，计算所有新插入的account表的amount值之和，触发器的名称为ins_sum，条件是在向表插入数据之前触发。

1.2、创建有多个执行语句的触发器

创建多个执行语句的触发器的语法如下：

CREATETRIGGERtrigger_name trigger_time trigger_eventONtb1_nameFOR EACH ROWBEGIN语句执行列表END

• trigger_name标识触发器的名称，用户自行指定；
• trigger_time标识触发时机，可以指定为before或after；
• trigger_event标识触发事件，包括INSERT、UPDATE和DELETE；
• tbl_name标识建立触发器的表名，即在哪张表上建立触发器；
• 触发器程序可以使用BEGIN和END作为开始和结束，中间包含多条语句。

创建一个包含多个执行语句的触发器，代码如下：

createtabletest1(a1int);createtabletest2(a2int);createtabletest3(a3intnotnullauto_incrementprimarykey);createtabletest4(a4intnotnullauto_incrementprimarykey,b4intdefault0);delimiter$$createtriggertestref beforeinsertontest1for each rowbegininsertintotest2seta2=NEW.a1;deletefromtest3wherea3=NEW.a1;updatetest4setb4=b4+1wherea4=NEW.a1;end$$delimiter;insertintotest3(a3)values(null),(null),(null),(null),(null),(null),(null),(null);insertintotest4(a4)values(0),(0),(0),(0),(0),(0),(0),(0),(0),(0);

上面的代码创建了一个名为testref的触发器。这个触发器的触发条件是在向表test1插入数据前执行触发器的语句，具体执行的代码如下：

insertintotest1values(1),(3),(1),(7),(1),(8),(4),(4);

4个表中的数据如下：

select*fromtest1;+----+|a1|+----+|1||3||1||7||1||8||4||4|+----+

select*fromtest2;+----+|a2|+----+|1||3||1||7||1||8||4||4|+----+

select*fromtest3;+----+|a3|+----+|2||5||6|+----+

select*fromtest4;+----+----+|a4|b4|+----+----+|1|3||2|0||3|1||4|2||5|0||6|0||7|1||8|1||9|0||10|0|+----+----+

执行结果显示，在向表test1插入记录的时候，test2、test3、test4都发生了变化。从这个例子看INSERT触发了触发器，向test2中插入了test1中的值，删除了test3中相同的内容，同时更新了test4中的b4，即与插入的值相同的个数。

2、查看触发器

查看触发器是指查看数据库中已存在的触发器的定义、状态和语法信息等。可以通过命令来查看已经创建的触发器。

两种查看触发器的方法，分别是SHOWTRIGGERS和在triggers表中查看触发器信息。

2.1、利用SHOW TRIGGERS语句查看触发器信息

通过SHOW TRIGGERS查看触发器的语句如下：

SHOWTRIGGERS;

通过SHOW TRIGGERS命令查看一个触发器，代码如下：

showtriggers;***************************[1.row]***************************Trigger|ins_sum Event|INSERTTable|account Statement|set@sum=@sum+NEW.amount Timing|BEFORE Created|2026-06-2521:50:31.090000***************************[1.row]***************************Trigger|ins_sum Event|INSERTTable|account Statement|set@sum=@sum+NEW.amount Timing|BEFORE Created|2026-06-2521:50:31.090000***************************[1.row]***************************Trigger|ins_sum Event|INSERTTable|account Statement|set@sum=@sum+NEW.amount Timing|BEFORE Created|2026-06-2521:50:31.090000sql_mode|ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTIONDefiner|root@%character_set_client|utf8mb4 collation_connection|utf8mb4_0900_ai_ciDatabaseCollation|utf8mb3_general_ci***************************[2.row]***************************Trigger|testref Event|INSERTTable|test1 Statement|begininsertintotest2seta2=NEW.a1;deletefromtest3wherea3=NEW.a1;updatetest4setb4=b4+1wherea4=NEW.a1;endTiming|BEFORE Created|2026-06-2521:58:25.100000sql_mode|ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTIONDefiner|root@%character_set_client|utf8mb4 collation_connection|utf8mb4_0900_ai_ciDatabaseCollation|utf8mb3_general_ci

创建一个简单的触发器，名称为trig_update，每次向account表更新数据之后都会向名称为myevent的数据表中插入一条记录，数据表myevent定义如下：

createtablemyevent(idint(11)defaultnull,evt_namechar(20)defaultnull);

创建触发器的执行代码如下：

createtriggertrig_updateafterupdateonaccountfor each rowinsertintomyeventvalues(1,'after update');

使用SHOW TRIGGERS命令查看触发器：

showtriggers \G;***************************[1.row]***************************Trigger|ins_sum Event|INSERTTable|account Statement|set@sum=@sum+NEW.amount Timing|BEFORE Created|2026-06-2521:50:31.090000sql_mode|ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTIONDefiner|root@%character_set_client|utf8mb4 collation_connection|utf8mb4_0900_ai_ciDatabaseCollation|utf8mb3_general_ci***************************[2.row]***************************Trigger|trig_update Event|UPDATETable|account Statement|insertintomyeventvalues(1,'after update')Timing|AFTERCreated|2026-06-2522:10:09.490000sql_mode|ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTIONDefiner|root@%character_set_client|utf8mb4

• Trigger表示触发器的名称，在这里两个触发器的名称分别为ins_sum和trig_update；
• Event表示激活触发器的事件，这里的两个触发事件为插入操作INSERT和更新操作UPDATE；
• Table表示激活触发器的操作对象表，这里都为account表；
• Timing表示触发器触发的时间，分别为插入操作之前（BEFORE）和更新操作之后（AFTER）​；
• Statement表示触发器执行的操作
• 还有一些其他信息，比如sql的模式、触发器的定义账户和字符集等

2.2、在triggers表中查看触发器信息

在MySQL中，所有触发器的定义都存在INFORMATION_SCHEMA数据库的TRIGGERS表格中，可以通过查询命令SELECT查看，具体的语法如下：

SELECT*FROMINFORMATION_SCHEMA.TRIGGERSWHEREcondition;

通过SELECT命令查看触发器，代码如下：

SELECT*FROMINFORMATION_SCHEMA.TRIGGERSWHERETRIGGER_NAME='trig_update'\G;***************************[1.row]***************************TRIGGER_CATALOG|def TRIGGER_SCHEMA|test_db TRIGGER_NAME|trig_update EVENT_MANIPULATION|UPDATEEVENT_OBJECT_CATALOG|def EVENT_OBJECT_SCHEMA|test_db EVENT_OBJECT_TABLE|account ACTION_ORDER|1ACTION_CONDITION|<null>ACTION_STATEMENT|insertintomyeventvalues(1,'after update')ACTION_ORIENTATION|ROWACTION_TIMING|AFTERACTION_REFERENCE_OLD_TABLE|<null>ACTION_REFERENCE_NEW_TABLE|<null>ACTION_REFERENCE_OLD_ROW|OLD ACTION_REFERENCE_NEW_ROW|NEW CREATED|2026-06-2522:10:09.490000SQL_MODE|ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTIONDEFINER|root@%CHARACTER_SET_CLIENT|utf8mb4 COLLATION_CONNECTION|utf8mb4_0900_ai_ci DATABASE_COLLATION|utf8mb3_general_ci

从上面的执行结果可以得知：

• TRIGGER_SCHEMA表示触发器所在的数据库；
• TRIGGER_NAME后面是触发器的名称；
• EVENT_OBJECT_TABLE表示在哪个数据表上触发；
• ACTION_STATEMENT表示触发器触发的时候执行的具体操作；
• ACTION_ORIENTATION是ROW，表示在每条记录上都触发；
• ACTION_TIMING表示触发的时刻是AFTER；
• 剩下的是和系统相关的信息。

也可以不指定触发器名称，这样将查看所有的触发器，命令如下：

SELECT*FROMINFORMATION_SCHEMA.TRIGGERS \G

这个命令会显示TRIGGERS表中所有的触发器信息。

3、触发器的使用

触发程序是与表有关的命名数据库对象，当表上出现特定事件时，将激活该对象。在某些触发程序的用法中，可用于检查插入到表中的值，或对更新涉及的值进行计算。

触发程序与表相关，当对表执行INSERT、DELETE或UPDATE语句时，将激活触发程序。可以将触发程序设置为在执行语句之前或之后激活。例如，可以在从表中删除每一行之前或在更新每一行之后激活触发程序。

创建一个在account表插入记录之后更新myevent数据表的触发器，代码如下：

createtriggertrig_insertafterinsertonaccountfor each rowinsertintomyeventvalues(2,'after insert');

上面的代码创建了一个trig_insert触发器，在向表account插入数据之后会向表myevent插入一组数据，代码执行如下：

insertintoaccountvalues(1,1.00),(2,2.00);select*frommyevent;+----+--------------+|id|evt_name|+----+--------------+|2|afterinsert||2|afterinsert|+----+--------------+

从执行的结果来看，创建了一个名称为trig_insert的触发器，在向account插入记录之后进行触发，执行的操作是向表myevent插入一条记录。

4、删除触发器

使用DROP TRIGGER语句可以删除MySQL中已经定义的触发器，删除触发器语句的基本语法格式如下：

DROPTRIGGER[schema_name.]trigger_name

• schema_name表示数据库名称，是可选的。如果省略了schema，将从当前数据库中舍弃触发程序；
• trigger_name是要删除的触发器的名称;

删除一个触发器，代码如下：

droptriggertest_db.ins_sum;Query OK,0rowsaffectedTime:0.072s

5、综合案例

下面是创建触发器的实例，每更新一次persons表的num字段后都要更新sales表对应的sum字段。其中，persons表结构如表所示：

sales表结构如表所示：

persons表内容如表所示：

1. 创建一个业务统计表persons。

创建一个业务统计表persons，代码如下：

createtablepersons(namevarchar(40),numint);

2. 创建一个销售额表sales。

创建一个销售额表sales，代码如下：

createtablesales(namevarchar(40),sumint);

3. 创建一个触发器。

创建一个触发器，在更新过persons表的num字段后，更新sales表的sum字段，代码如下：

createtriggernum_sumafterinsertonpersonsfor each rowinsertintosalesvalues(NEW.name,7*NEW.num);

4. 向persons表中插入记录。

插入新的记录后，更新销售额表。

insertintopersonsvalues('xiaoxiao',20),('xiaohua',69);

结果如下：

select*frompersons;+----------+-----+|name|num|+----------+-----+|xiaoxiao|20||xiaohua|69|+----------+-----+select*fromsales;+----------+-----+|name|sum|+----------+-----+|xiaoxiao|140||xiaohua|483|+----------+-----+

从执行的结果来看，在persons表插入记录之后，num_sum触发器计算插入到persons表中的数据，并将结果插入到sales表中相应的位置。

6、常见问题

6.1、使用触发器时要特别注意的事项

使用触发器的时候需要注意，对于相同的表，相同的事件只能创建一个触发器，比如对表account创建了一个BEFORE INSERT触发器，那么如果对表account再次创建一个BEFORE INSERT触发器，MySQL将会报错，此时，只可以在表account上创建AFTER INSERT或者BEFORE UPDATE类型的触发器。灵活地运用触发器将为操作省去很多麻烦。

6.2、及时删除不再需要的触发器

触发器定义之后，每次执行触发事件都会激活触发器并执行触发器中的语句。如果需求发生变化，而触发器没有进行相应的改变或者删除，则触发器仍然会执行旧的语句，从而会影响新的数据完整性。因此，要将不再使用的触发器及时删除。