MySQL 全面瓦解
首页 死磕 Java 优质系列 Java 技术总览 Java 面试宝典 关于我

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

转
 2024-04-04  阅读(4)
©版权
原文作者：Brand 原文地址： https://www.cnblogs.com/wzh2010/p/14052857.html

关于触发器

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

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

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

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

触发器的使用

创建触发器

创建触发器的语法如下：

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

说明：

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

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

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

Insert类型触发器：

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

Update类型触发器：

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

Delete类型触发器：

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

查看触发器

查看全部触发器
    1 show triggers; --语法 

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

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

删除触发器

    1 drop trigger trigger_name;   --语法 

    1 mysql> drop trigger trig_after_insert;
    2 Query OK, 0 rows affected
    3 
    4 mysql> show create trigger trig_after_insert;
    5 1360 - Trigger does not exist 

使用时的注意点

NEW / OLD的使用区别

触发器针对的是数据库中的每一行记录，每行数据在操作前后都会有一个对应的状态，触发器将没有操作之前的状态保存到 old 关键字中，将操作后的状态保存到 new 中

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

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

触发器类型 new和old的使用
INSERT型触发器 没有old，只有new，new表示将要(插入前)或者已经增加(插入后)的数据
UPDATE型触发器 既有old也有new，old表示更新之前的数据，new表示更新之后的数据
DELETE型触发器 没有new，只有old，old表示将要(删除前)或者已经被删除(删除后)的数据

其实我们上面已经用过 new/old 来创建触发器并获取数据，我们根据这个表格重新来修改下那个更新触发器(trig_after_update),输出修改前和修改后的内容比较：

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

无法触发对同表的修改

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

     1 mysql> 
     2 /*这边声明SQL脚本的结束符为// */
     3 DELIMITER //
     4 DROP TRIGGER IF EXISTS trig_after_insert;
     5 CREATE TRIGGER trig_after_insert AFTER INSERT ON students FOR EACH ROW
     6 BEGIN
     7  update students set score = score+1 where studentid= new.studentid;
     8 END //
     9 Query OK, 0 rows affected
    10 
    11 mysql> 
    12 /*重置SQL脚本的结束符为; */
    13 DELIMITER ;
    14 Query OK, 0 rows affected
    15 
    16 mysql> 
    17 /*插入一条数据之后因为触发了对本表的修改，所以报错了 */
    18 insert into students(studentname,score,classid) values('trigger2',101,0);
    19 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、触发器有BEFORE触发器和AFTER触发器的却别，所以它的执行步骤是先执行 BEFORE触发器，再执行业务脚本，最后执行AFTER触发器。需要注意的是 某一个步骤错误后就不再执行了， 如果是事务表，会被回滚，如果是非事务表，那么就无法回滚了，数据可能有不一致性。

触发器的两种限制

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

Java 面试宝典是大明哥全力打造的 Java 精品面试题，它是一份靠谱、强大、详细、经典的 Java 后端面试宝典。它不仅仅只是一道道面试题，而是一套完整的 Java 知识体系，一套你 Java 知识点的扫盲贴。

它的内容包括：

  • 大厂真题：Java 面试宝典里面的题目都是最近几年的高频的大厂面试真题。
  • 原创内容：Java 面试宝典内容全部都是大明哥原创，内容全面且通俗易懂，回答部分可以直接作为面试回答内容。
  • 持续更新：一次购买，永久有效。大明哥会持续更新 3+ 年，累计更新 1000+，宝典会不断迭代更新，保证最新、最全面。
  • 覆盖全面：本宝典累计更新 1000+，从 Java 入门到 Java 架构的高频面试题，实现 360° 全覆盖。
  • 不止面试：内容包含面试题解析、内容详解、知识扩展，它不仅仅只是一份面试题，更是一套完整的 Java 知识体系。
  • 宝典详情：https://www.yuque.com/chenssy/sike-java/xvlo920axlp7sf4k
  • 宝典总览：https://www.yuque.com/chenssy/sike-java/yogsehzntzgp4ly1
  • 宝典进展：https://www.yuque.com/chenssy/sike-java/en9ned7loo47z5aw

目前 Java 面试宝典累计更新 400+ 道，总字数 42w+。大明哥还在持续更新中，下图是大明哥在 2024-12 月份的更新情况：

想了解详情的小伙伴，扫描下面二维码加大明哥微信【daming091】咨询

同时，大明哥也整理一套目前市面最常见的热点面试题。微信搜[大明哥聊 Java]或扫描下方二维码关注大明哥的原创公众号[大明哥聊 Java] ，回复【面试题】 即可免费领取。

阅读全文
文章标签： MySQL 核心原理