mysql触发器语法规则（MySQL 触发器的使用及需要注意的地方）

mysql触发器语法规则

MySQL 触发器的使用及需要注意的地方

目录

• 关于触发器
• 触发器的使用
  • 创建触发器
  • 查看触发器
  • 删除触发器
• 使用时的注意点
  • NEW / OLD的使用区别
  • 无法触发对同表的修改
• 小结
  • 触发器的两种限制

 

关于触发器

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

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

触发器（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 3 4 5 6 7 8 9 10 11 12

/*先创建一个日志表，用于触发器触发时候填入值*/ 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 affected

Insert类型触发器：

?

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

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 set

Update类型触发器：

?

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

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

Delete类型触发器：

?

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

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 set

 

查看触发器

查看全部触发器

?

1	show triggers; --语法

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

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 set

查看触发器的创建语句

?

1	show create trigger trigger_name;  --语法

?

1 2 3 4 5 6 7 8 9 10

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 set

 

删除触发器

?

1	drop trigger trigger_name;  --语法

?

1 2 3 4 5

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

 

使用时的注意点

 

NEW / OLD的使用区别

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

?

1 2	new.cname --新增行（或修改前的行）的某列数据 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 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> /*这边声明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 set

 

无法触发对同表的修改

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

mysql> /*这边声明SQL脚本的结束符为// */ DELIMITER // DROP TRIGGER IF EXISTS trig_after_insert; 标签：mysql 触发器 上一篇：python中的eval函数的用法（Python eval的常见错误封装及利用原理详解） 下一篇：apache服务配置详解（apache配置开启网站服务步骤） 您可能感兴趣 在php中与数据库连接的技术（PHP7.0连接DB操作实例分析基于mysqli） mysql主从同步工作原理（MySQL是如何实现主备同步） centos7.5安装mysql教程（CentOS8下MySQL 8.0安装部署的方法） mysql创建数据库基本指令（MySql 新建用户与数据库的实际操作步骤） mysqlbinlog优缺点（MySQL Binlog 日志处理工具对比分析） mysql与oracle体系结构（详解MySQL实时同步到Oracle解决方案） mysql 慢查询排查方法（MYSQL慢查询和日志实例讲解） mysql创建存储过程的代码（MySQL修改存储过程的详细步骤） mysql数据库死锁原理（MySQL数据库锁机制原理解析） mysql最佳配置（详解DBeaver连接MySQL8以上版本以及解决可能遇到的问题） navicat怎么和mysql连接（Navicat Premium远程连接MySQL数据库的方法） mysql索引失效原因（MySQL索引失效的几种情况详析） mysql时间戳和datetime对比（mysql中 datatime与timestamp的区别说明） mysql数据备份的几种方式（MySQL数据库备份过程的注意事项） mysql order by id查询变慢（MySQL不使用order by实现排名的三种思路总结） mysql安装详解（MySQL Router的安装部署） 吴启华与曾舜晞两代张无忌同框，戏里经典的他却没活出原著的潇洒（吴启华与曾舜晞两代张无忌同框） 经常发这三种 朋友圈 的人，要迅速屏蔽（经常发这三种朋友圈） 有种尴尬叫朋友圈忘屏蔽，大学生上演社死现场，父母亲自下场吐槽（有种尴尬叫朋友圈忘屏蔽） 朋友圈屏蔽你的人，可以直接看淡了（朋友圈屏蔽你的人） 金球奖只青睐那些会戴珠宝的女人（金球奖只青睐那些会戴珠宝的女人） 浙江省一个县，人口超40万，建县历史超1100年（浙江省一个县人口超40万） 热门推荐 python编程ide工具（这6款Python IDE&代码编辑器，你都用过吗？） phpStudy启动失败时的解决方法 提示缺vc9运行库（phpStudy启动失败时的解决方法 提示缺vc9运行库） dedecms频道封面不能修改内容（DEDECMS内容页分页过多、过长问题最佳解决方案） 虚拟主机管理系统干什么用（五大免费主机管理系统优缺点对比及推荐） vue用手动上传图片（vue上传图片文件的多种实现方法） 阿里云主机Windows 2008 32位 64位自助正版激活图文教程（阿里云主机Windows 2008 32位 64位自助正版激活图文教程） Oracle TO_CHAR的使用 ASP.NET Web API是什么 dedecms如何使用标签（dedecms 移动文章后，原来生成的HTML依然存在解决方法） python pandas读取数据库表（Python3.5 Pandas模块之DataFrame用法实例分析） 排行榜 1 mysql用户删除了如何设置（MySQL两种删除用户语句的区别delete user和drop user） 2 mysql数据库下载安装步骤（MySQL数据库压缩版本安装与配置详细教程） 3 pythonmysql使用教程（Python异步操作MySQL示例使用aiomysql） 4 mysql将字符串转换成整数（MYSQL字符串强转的方法示例） 5 mysql安装详解（MySQL Router的安装部署） 6 mysql order by id查询变慢（MySQL不使用order by实现排名的三种思路总结） 7 mysql数据备份的几种方式（MySQL数据库备份过程的注意事项） 8 mysql时间戳和datetime对比（mysql中 datatime与timestamp的区别说明） 9 mysql索引失效原因（MySQL索引失效的几种情况详析） 首页 编程学习 Web前端 数据库 软件设计 开心学习 ©2013-2021 保留所有权利