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主键要设置自增列（浅谈MySQL中的自增主键用完了怎么办） mysql创建数据库基本指令（MySql 新建用户与数据库的实际操作步骤） mysql架构示意图（MySQL创建横向直方图的解决方案） mysql中innodb四种事务级别（MySQL InnoDB 锁的相关总结） 如何正常查看mysql存储数据文件（Mysql文件存储图文详解） mysqlinnodb锁使用教程（MySQL Innodb关键特性之插入缓冲insert buffer） MySQL自定义函数 mysqljson字段查询（Mysql 查询JSON结果的相关函数汇总） docker中mysql连接命令（docker 如何修改mysql的root密码） ubuntu20.2安装mysql（Ubuntu 14.04下mysql安装配置教程） mysql重启启动失败（MySQL8.0无法启动3534的解决方法） mysql客户端怎么运行程序（MySQL 如何连接对应的客户端进程） mysql8.0.15安装详细教程（Mysql8.0.22解压版安装教程小白专用） mysql缓存是什么（详解mysql查询缓存简单使用） mysql多表连接优化（浅谈Mysql多表连接查询的执行细节） iis6双php版本的设置（云主机IIS7.5支持PHP5.3以上版本和MYSQL） 云南旅游攻略（云南旅游攻略5天攻略） 收藏 春节假期，这些景区巨划算（收藏春节假期这些景区巨划算） 景区游玩，这些安全知识要牢记（这些安全知识要牢记） 各地出招烘 热 旅游（各地出招烘热旅游） 2021款起亚霸锐到店了 更换车标，竞争宝马X5有戏吗（2021款起亚霸锐到店了） 新款起亚霸锐实拍，比普拉多气派，配3.0T V6，引入国内或许能大卖（新款起亚霸锐实拍） 热门推荐 外企面试需要注意什么 css3动画按钮加光晕效果（CSS3 制作的悬停缩放特效） navicat连接报错10038（解决Navicat for Mysql连接报错1251的问题连接失败） js实时获取页面宽度（JavaScript获取网页的宽高及如何兼容详解） 如何编写docker-compose（使用Docker Compose搭建 Confluence的教程） pandas删除特定条件的行（pandas删除指定行详解） docker中的mongodb（Docker mongoDB 4.2.1 安装并收集springboot日志的步骤详解） dedecms模板特点（为DedeCMS换上精美多样的提示信息窗口） vue3.0路线图（Vue3.0 自己实现放大镜效果案例讲解） sqlleftjoin详解（SQL JOIN 连接详细介绍及简单使用实例） 排行榜 1 navicat连接mysql报1045（解决Navicat for MySQL 连接 MySQL 报2005错误的问题） 2 mysql 操作系统时区（mysql时区查看与设置方法） 3 mysql分区怎么实现（MySql分表、分库、分片和分区知识深入详解） 4 docker创建mysql环境（docker上部署MySQL的示例） 5 iis6双php版本的设置（云主机IIS7.5支持PHP5.3以上版本和MYSQL） 6 mysql多表连接优化（浅谈Mysql多表连接查询的执行细节） 7 mysql缓存是什么（详解mysql查询缓存简单使用） 8 mysql8.0.15安装详细教程（Mysql8.0.22解压版安装教程小白专用） 9 mysql客户端怎么运行程序（MySQL 如何连接对应的客户端进程） 首页 编程学习 Web前端 数据库 软件设计 开心学习 ©2013-2021 保留所有权利