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配置开启网站服务步骤） 您可能感兴趣 MYSQL中常用的强制性操作 navicat中如何使用sql语句（Navicat Premium操作MySQL数据库执行sql语句） mysql5.7.36详细安装（CenOS6.7下mysql 8.0.22 安装配置方法图文教程） mysql行级锁使用教程（浅析MySQL的lru链表） oracle和mysql查询所有表（Oracle、MySQL和SqlServe三种数据库分页查询语句的区别介绍） mysqlroot本地远程都可登陆（mysql5.7 设置远程访问的实现） mysqljoin语句用法（MySQL的join buffer原理） mysql有哪些约束（MySQL完整性约束的定义与实例教程） mysql数据类型图解（MySQL数据库主从技术GTID大揭秘） mysql各种类型设置（MySQL sql_mode的使用详解） mysql定时任务 mysqlbinlog怎么分析（MySQL中使用binlog时格式该如何选择） mysqlinnodb锁使用教程（MySQL Innodb关键特性之插入缓冲insert buffer） 最新版MySQL 8.0.22下载安装超详细教程(Windows 64位)（最新版MySQL 8.0.22下载安装超详细教程Windows 64位） docker怎么连接mysql（docker如何安装mysql） mysql5.7.19下载及安装教程（Apache2.2.16+PHP5.3.3+MySQL5.1.49的配置方法） 菲律宾游学（菲律宾游学中介机构） 菲律宾美食（菲律宾美食排行榜前十名） 菲律宾特产（菲律宾特产最值得买回国） 越南特产（越南特产首饰） TVB负评王连续挑战警察角色《使徒行者3》中将饰演卧底（TVB负评王连续挑战警察角色使徒行者3中将饰演卧底） 《精英律师》剧照首曝光，实力演员飙戏，演绎律政职场百态（精英律师剧照首曝光） 热门推荐 ASP.NET实现FTP上传文件 react基础知识详解（如何深入理解React的ref 属性） laravel查询数据库视图（Laravel5.7 数据库操作迁移的实现方法） idea向数据库中插入中文报错（Idea连接MySQL数据库出现中文乱码的问题） django后台运行命令（Django框架实现的普通登录案例使用POST方法） docker 更新容器到镜像（Docker容器如何更新打包并上传到阿里云） phpdate函数使用方法（PHP中strtr与str_replace函数运行性能简单测试示例） laravel查询构建器原理和使用（阿里对象存储OSS在laravel框架中的使用方法） jQuery对象和DOM的相互转换 安装laravel框架（laravel框架的安装与路由实例分析） 排行榜 1 对mysql性能优化的看法（聊聊MySQL的COUNT的性能，看看怎么最快?） 2 django mysql设置（Django框架使用mysql视图操作示例） 3 mysql的连接代码（MySQL 连接查询的原理和应用） 4 mysql总是报错error（MySQL 5.6主从报错的实战记录） 5 mysql5.7.19下载及安装教程（Apache2.2.16+PHP5.3.3+MySQL5.1.49的配置方法） 6 docker怎么连接mysql（docker如何安装mysql） 7 最新版MySQL 8.0.22下载安装超详细教程(Windows 64位)（最新版MySQL 8.0.22下载安装超详细教程Windows 64位） 8 mysqlinnodb锁使用教程（MySQL Innodb关键特性之插入缓冲insert buffer） 9 mysqlbinlog怎么分析（MySQL中使用binlog时格式该如何选择） 首页 编程学习 Web前端 数据库 软件设计 开心学习 ©2013-2021 保留所有权利