关于触发器

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

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

触发器(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。

MySQL全面瓦解17:触发器相关的更多相关文章

  1. MongoDB备份(mongoexport)与恢复(mongoimport)

    1.备份恢复工具介绍:mongoexport/mongoimportmongodump/mongorestore(本......

  2. PostgreSQL 使用raise函数打印字符串

    raise函数在PostgreSQL中,该函数用于打印字符串,类似于Java中的System.out.println......

  3. MySQL查询截取分析

    一、查询优化1,mysql的调优大纲慢查询的开启并捕获explain+慢SQL分析show profile查询SQL......

  4. MySQL中外键的创建、约束以及删除

    前言在MySQL 3.23.44版本后,InnoDB引擎类型的表支持了外键约束。外键的使用条件:1.两个表必须是In......

  5. PostgreSQL 实现给查询列表增加序号操作

    利用 ROW_NUMBER() over( ) 给查询序列增加排序字段SELECT ROW_NUMBER() ove......

  6. MYSQL 字符集设置(终端的字符集)

    每次利用终端 创建数据库或者创建表的时候,字符集都是latin1(不指定字符集的时候)如下:查看当前数据库的字符集c......

  7. 阿里面试:MySQL如何设计索引更高效?

    前言数据库系列更新到现在我想大家对所有的概念都已有个大概认识了,这周我在看评论的时候我发现有个网友的提问我觉得很有意......

  8. MySql8 WITH RECURSIVE递归查询父子集的方法

    背景开发过程中遇到类似评论的功能是,需要时用查询所有评论的子集。不同数据库中实现方式也不同,本文使用Mysql数据库......

  9. MySQL多版本并发控制——MVCC机制分析

    MVCC,即多版本并发控制(Multi-Version Concurrency Control)指的是,通过版本链维......

  10. docker+mysql集群+读写分离+mycat管理+垂直分库+负载均衡

    依然如此,只要大家跟着我的步骤一步步来,99.99999%是可以测试成功的centos6.8已不再维护,可能很多人的......

随机推荐

  1. JS实现公告上线滚动效果

    本文实例为大家分享了JS实现公告上线滚动效果的具体代码,供大家参考,具体内容如下实现的效果如下,新闻公告上下滚动。代......

  2. ASP.NET Core 3.1 中间件

    参考微软官方文档 :https://docs.microsoft.com/zh-cn/aspnet/core/fun......

  3. windows打包脚本出现 /bin/sh^M: 坏的解释器: 没有那个文件或目录 错误

    1.错误描述我在Windows 10 系统下打包dolphinscheduler,上传到centos7解压之后,执行......

  4. MySQL中外键的创建、约束以及删除

    前言在MySQL 3.23.44版本后,InnoDB引擎类型的表支持了外键约束。外键的使用条件:1.两个表必须是In......

  5. 在使用内模块的时候需要先将所需的内置模块进行引入、OS模块在nodejs中OS模块提供了与操作系统相关的属性和方法/......

  6. Python学习(9)(元组、定义、常用操作、循环遍历、应用场景、元组与列表的转换)

    Python学习(9)一、python的元组 定义二、python的元组 常用操作三、python 元组的循环遍历四......

  7. python实现excel公式格式化的示例代码

    之前跟一些小伙伴有个讨论:大概就是很多跟数据打交道的朋友都面对过很复杂的excel公式,有时嵌套层数特别多,肉眼观看......

  8. java DelayQueue的原理浅析

    在对DelayQueue延迟功能的使用上,很多人不能后完全理解延迟的一些功能使用,这里我们深入来挖掘一下DelayQ......

  9. .NET斗鱼直播弹幕客户端(2021)

    之前的.NET斗鱼弹幕不能用了?看这篇文章即可。.NET斗鱼直播弹幕客户端(2021)离之前更新的两篇《.NET斗鱼......

  10. 分别使用 Python 和 Math.Net 调用优化算法

    1. Rosenbrock 函数在数学最优化中,Rosenbrock 函数是一个用来测试最优化算法性能的非凸函数,由......