MySQL全面瓦解17：触发器相关

爱新觉羅丶高

发布于 2022-7-27 15:21

浏览

0收藏

关于触发器
现实开发中我们经常会遇到这种情况，比如添加、删除和修改信息的时候需要记录日志，我们就要在完成常规的数据库逻辑操作之后再去写入日志表，这样变成了两步操作，更复杂了。

又比如删除一个人员信息的时候，需要将他的购物记录、收货地址、收藏夹等都删了，这个连续的操作容易出错，一致性和完整性不好保证。这时候就要使用触发器，既可以免去一堆的业务逻辑代码，又能更好的保证数据完整性。

触发器（trigger）是一种与表有关的数据库对象：在满足定义条件时触发某种操作，并执行触发器中定义的语句集合。触发器的这种特性可以协助应用在数据库端保证数据的完整性。

它是一种与表事件相关的特殊的存储过程，当对一个表进行操作（ insert，delete， update）时就会激活执行。

触发器的使用
创建触发器
创建触发器的语法如下：

CREATE TRIGGER trigger_name trigger_time trigger_event ON t_name FOR EACH ROW trigger_stmt
1.

说明：

trigger_name：触发器名称

tirgger_time：触发执行时间点，数据操作前(BEFORE) 或者数据操作后(AFTER)

trigger_event：触发事件，增(INSERT)、删(DELETE)、改(UPDATE)

t_name：指的是在 t_name 这张表上建立触发器

trigger_stmt：触发器的程序体，可以是一条SQL语句或者是用BEGIN和END包含的多条语句

触发器只能创建在永久表（Permanent）上，不能创建在临时表（Temporary）上

FOR EACH ROW 固定表达式，表示任何一条记录上的操作满足触发事件都会触发该触发器

tirgger_time有两种类型，trigger_event有三种类型，所以他们一共有6种组合：BEFORE INSERT,BEFORE DELETE,BEFORE UPDATE,AFTER INSERT,AFTER DELETE,AFTER UPDATE

示例（先创建一个日志表，用于触发器触发时候填入值）：

 /*先创建一个日志表，用于触发器触发时候填入值*/
 mysql> DROP TABLE IF EXISTS `TriggerLog`;
 Query OK, 0 rows affected
 
 mysql> CREATE TABLE `TriggerLog`
  (
     `id` INT auto_increment PRIMARY KEY,
   `trigger_time` VARCHAR(30),
    `trigger_event` VARCHAR(30), 
   `memo` VARCHAR(200)   
 );
 Query OK, 0 rows affected1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.

Insert类型触发器：

 mysql> 
 /*这边声明SQL脚本的结束符为// */
 DELIMITER //
 DROP TRIGGER IF EXISTS trig_after_insert;
 CREATE TRIGGER trig_after_insert AFTER INSERT ON students FOR EACH ROW
 BEGIN
   insert into `TriggerLog`(`trigger_time`,`trigger_event`,`memo`) values ('after','insert',concat('new student info,id:',cast(new.studentid as char)));
 END //
 Query OK, 0 rows affected
 
 mysql> 
 /*重置SQL脚本的结束符为; */
 DELIMITER ;
 Query OK, 0 rows affected
 
 mysql> 
 /*插入一条数据 */
 insert into students(studentname,score,classid) values('trigger1',100,0);
 Query OK, 1 row affected
 

 mysql> 
 /*查询日志表看是否有触发写入 */
 select * from `TriggerLog`;
 +----+--------------+---------------+------------------------+
 | id | trigger_time | trigger_event | memo                   |
 +----+--------------+---------------+------------------------+
 |  1 | after        | insert        | new student info,id:21 |
 +----+--------------+---------------+------------------------+
 1 row in set1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.

Update类型触发器：

mysql> 
 /*这边声明SQL脚本的结束符为// */
DELIMITER //
 DROP TRIGGER IF EXISTS trig_after_update;
 CREATE TRIGGER trig_after_update AFTER UPDATE ON students FOR EACH ROW
BEGIN
   insert into `TriggerLog`(`trigger_time`,`trigger_event`,`memo`) values ('after','update',concat('update student info,id:',cast(new.studentid as char)));
 END //
 Query OK, 0 rows affected
 
 mysql> 
 /*重置SQL脚本的结束符为; */
 DELIMITER ;
 Query OK, 0 rows affected
 
 mysql> 
/*更新数据 */
 update students set score=99 where studentname='trigger1';
 Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0
 
 mysql> 
 /*查询日志表看是否更新的时候有触发写入 */
 select * from `TriggerLog`;
 +----+--------------+---------------+---------------------------+
 | id | trigger_time | trigger_event | memo                      |
 +----+--------------+---------------+---------------------------+
 |  1 | after        | insert        | new student info,id:21    |
 |  2 | after        | update        | update student info,id:21 |
+----+--------------+---------------+---------------------------+
 2 rows in set 1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.

Delete类型触发器：

mysql> 
 /*这边声明SQL脚本的结束符为// */
DELIMITER //
 DROP TRIGGER IF EXISTS trig_after_delete;
 CREATE TRIGGER trig_after_delete AFTER DELETE ON students FOR EACH ROW
 BEGIN
   insert into `TriggerLog`(`trigger_time`,`trigger_event`,`memo`) values ('after','update',concat('delete student info,id:',cast(old.studentid as char)));
 END //
 Query OK, 0 rows affected

 mysql> 
 /*重置SQL脚本的结束符为; */
 DELIMITER ;
 Query OK, 0 rows affected
 
 mysql> 
 /* 删除数据 */
 delete from students where studentid=21;
Query OK, 1 row affected
 
 mysql> 
/*查询日志看删除是否有触发写入*/
 select * from `TriggerLog`;
 +----+--------------+---------------+---------------------------+
 | id | trigger_time | trigger_event | memo                      |
 +----+--------------+---------------+---------------------------+
 |  1 | after        | insert        | new student info,id:21    |
 |  2 | after        | update        | update student info,id:21 |
 |  3 | after        | update        | delete student info,id:21 |
 +----+--------------+---------------+---------------------------+
 3 rows in set1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.

查看触发器
查看全部触发器

show triggers; --语法 1.

 mysql> show triggers;
 +-------------------+--------+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+---------+--------------------------------------------+----------------+----------------------+----------------------+--------------------+
 | Trigger           | Event  | Table    | Statement                                                                                                                                                          | Timing | Created | sql_mode                                   | Definer        | character_set_client | collation_connection | Database Collation |
 +-------------------+--------+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+---------+--------------------------------------------+----------------+----------------------+----------------------+--------------------+
 | trig_after_insert | INSERT | students | BEGIN
   insert into `TriggerLog`(`trigger_time`,`trigger_event`,`memo`) values ('after','insert',concat('new student info,id:',cast(new.studentid as char)));
 END    | AFTER  | NULL    | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | root@localhost | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
 | trig_after_update | UPDATE | students | BEGIN
   insert into `TriggerLog`(`trigger_time`,`trigger_event`,`memo`) values ('after','update',concat('update student info,id:',cast(new.studentid as char)));
 END | AFTER  | NULL    | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | root@localhost | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
 | trig_after_delete | DELETE | students | BEGIN
   insert into `TriggerLog`(`trigger_time`,`trigger_event`,`memo`) values ('after','update',concat('delete student info,id:',cast(old.studentid as char)));
 END | AFTER  | NULL    | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | root@localhost | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
 +-------------------+--------+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+---------+--------------------------------------------+----------------+----------------------+----------------------+--------------------+
 3 rows in set1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.

查看触发器的创建语句

show create trigger trigger_name;   --语法
1.

 mysql> show create trigger trig_after_insert;


 +-------------------+--------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
 | Trigger           | sql_mode                                   | SQL Original Statement                                                                                                                                                                                                                                           | character_set_client | collation_connection | Database Collation |
 +-------------------+--------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
 | trig_after_insert | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` TRIGGER trig_after_insert AFTER INSERT ON students FOR EACH ROW
  BEGIN
    insert into `TriggerLog`(`trigger_time`,`trigger_event`,`memo`) values ('after','insert',concat('new student info,id:',cast(new.studentid as char)));
 END | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
  +-------------------+--------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
 1 row in set1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.

删除触发器

 drop trigger trigger_name;   --语法 
 mysql> drop trigger trig_after_insert;
 Query OK, 0 rows affected
 
 mysql> show create trigger trig_after_insert;
51360 - Trigger does not exist 1.
2.
3.
4.
5.
6.

使用时的注意点
NEW / OLD的使用区别
触发器针对的是数据库中的每一行记录，每行数据在操作前后都会有一个对应的状态，触发器将没有操作之前的状态保存到 old 关键字中，将操作后的状态保存到 new 中

 new.cname --新增行（或修改前的行）的某列数据
 old.cname --删除行（或修改后的行）的某列数据1.
2.

需要注意的是，old 和 new 不是所有触发器都有

MySQL全面瓦解17：触发器相关-鸿蒙开发者社区
其实我们上面已经用过 new/old 来创建触发器并获取数据，我们根据这个表格重新来修改下那个更新触发器(trig_after_update),输出修改前和修改后的内容比较：

 mysql> 
  /*这边声明SQL脚本的结束符为// */
  DELIMITER //
  DROP TRIGGER IF EXISTS trig_after_update;
  CREATE TRIGGER trig_after_update AFTER UPDATE ON students FOR EACH ROW
  BEGIN
    insert into `TriggerLog`(`trigger_time`,`trigger_event`,`memo`) values ('after','update',concat('from:',old.studentname,',',old.score,' ','to:',new.studentname,',',new.score));
  END //
  Query OK, 0 rows affected
 
 mysql> 
 /*重置SQL脚本的结束符为; */
 DELIMITER ;
 Query OK, 0 rows affected
 
 mysql> 
 /*更新成绩和名称 */
 update students set score=106,studentname='trigger2' where studentid=17;
 Query OK, 1 row affected
 Rows matched: 1  Changed: 1  Warnings: 0
 
 mysql> 
 /*根据old和new 对比更新前后的值 */
 select * from `TriggerLog`;
 +----+--------------+---------------+--------------------------------------+
| id | trigger_time | trigger_event | memo                                 |
 +----+--------------+---------------+--------------------------------------+
 |  1 | after        | insert        | new student info,id:21               |
 |  2 | after        | update        | update student info,id:21            |
 |  3 | after        | update        | delete student info,id:21            |
 |  4 | after        | update        | from:test2,101.00 to:trigger2,106.00 |
 +----+--------------+---------------+--------------------------------------+
 4 rows in set1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.

无法触发对同表的修改
MySQL 的触发器中不能对本表进行 insert、update 和 delete 操作，否则会报错

 mysql> 
  /*这边声明SQL脚本的结束符为// */
 DELIMITER //
  DROP TRIGGER IF EXISTS trig_after_insert;
  CREATE TRIGGER trig_after_insert AFTER INSERT ON students FOR EACH ROW
  BEGIN
   update students set score = score+1 where studentid= new.studentid;
  END //
  Query OK, 0 rows affected

 mysql> 
 /*重置SQL脚本的结束符为; */
DELIMITER ;
Query OK, 0 rows affected
 
 mysql> 
 /*插入一条数据之后因为触发了对本表的修改，所以报错了 */
 insert into students(studentname,score,classid) values('trigger2',101,0);
 1442 - Can't update table 'students' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.

小结
1、触发器可以通过数据库中的关联表实现级联更改，即一张表数据的改变会影响其他表的数据，如数据修改，数据统计，数据复制等。
2、可以保证数据安全，并进行安全校验，限制用户具有操作数据库的某种权利。
3、可以对复杂逻辑的实现进行数据完整性检查和约束。
4、使用触发器的要应需而用，如果过分依赖触发器，会影响数据库的结构，增加数据库的执行和维护成本。
5、触发器有BEFORE触发器和AFTER触发器的区别，所以它的执行步骤是先执行 BEFORE触发器，再执行业务脚本，最后执行AFTER触发器。需要注意的是某一个步骤错误后就不再执行了，如果是事务表，会被回滚，如果是非事务表，那么就无法回滚了，数据可能有不一致性。

触发器的两种限制

1、触发程序不能调用将数据返回客户端的存储程序，也不能使用采用CALL语句的动态SQL语句，但是允许存储程序通过参数将数据返回触发程序，也就是存储过程或者函数通过OUT或者INOUT类型的参数将数据返回触发器是可以的，但是不能调用直接返回数据的过程。
2、不能在触发器中使用以显示或隐式方式开始或结束事务的语句，如START TRANS-ACTION,COMMIT或ROLLBACK。

文章转载自公众号：架构与思维

分类

标签

已于2022-7-27 15:21:03修改

51CTO

51CTO博客

51CTO学堂

MySQL全面瓦解17：触发器相关

订阅鸿蒙技术特刊，精选内容抢先看