认真学习MySQL 中的触发器

本文涉及的产品
云数据库 RDS SQL Server,独享型 2核4GB
云数据库 RDS MySQL Serverless,0.5-2RCU 50GB
简介: 认真学习MySQL 中的触发器

【1】触发器定义

触发器:trigger,事先为某张表绑定好一段代码。当表中的某些内容发生改变的时候(增删改),系统自动触发代码执行。 即,触发器是基于(依赖于)表的。

所有的触发器(针对数据库服务器而不是数据库)都会保存到数据库information_schema的triggers表中。

① 触发器的事件类型

三种类型:增删改–insert,delete和update。

MySQL 除了对 INSERT、UPDATE、DELETE 基本操作进行定义外,还定义了 LOAD DATA 和 REPLACE 语句,这两种语句也能引起上述6中类型的触发器的触发。

LOAD DATA 语句用于将一个文件装入到一个数据表中,相当与一系列的 INSERT 操作。


REPLACE 语句一般来说和 INSERT 语句很像,只是在表中有 primary key 或 unique 索引时,如果插入的数据和原来 primary key 或 unique 索引一致时,会先删除原来的数据,然后增加一条新数据,也就是说,一条 REPLACE 语句有时候等价于一条。

INSERT 语句,有时候等价于一条 DELETE 语句加上一条 INSERT 语句。

INSERT 型触发器:插入某一行时激活触发器,可能通过 INSERT、LOAD DATA、REPLACE 语句触发;
UPDATE 型触发器:更改某一行时激活触发器,可能通过 UPDATE 语句触发;
DELETE 型触发器:删除某一行时激活触发器,可能通过 DELETE、REPLACE 语句触发。

② 触发器的时间类型

两种类型:前后–before 和 after

after是先完成数据的增删改,再触发,触发器的语句晚于触发事件,无法影响前面触发事件的增删改动作;

before是先完成触发,再增删改,触发器的语句先于触发事件,我们就有机会判断,修改即将发生的操作

一张表中只能拥有一种触发时间的一种类型的触发器,可同时容纳不同触发时间的不同类型的触发器。即,一张表最多可有六个触发器。

【2】创建触发器

语法格式如下:

create trigger 触发器名字 触发时间 事件类型 on 表名 for each row
begin -- 代表左大括号:开始
--语句内容,每条语句都要使用分号结束;
end;

示例如下:

create TRIGGER tr_puser before insert  on p_user for EACH ROW
BEGIN
 set new.age = '0' ;
end;

插入数据前,将新插入的数据记录中age设置为0,那么建立触发器后,以后所有插入数据表age都将为0。

【3】查看触发器

语法格式如下:

show triggers [like pattern];

示例如下:

--查看所有触发器
show triggers;

20170628183328798.png查看触发器创建语句语法格式如下:

show create trigger 触发器名字;

示例如下:

show create trigger tr_puser;

20170628183710265.png

触发器记录

不管触发器是否触发了,只要当某种操作准备执行,系统就会将当前要操作的记录的当前状态和即将执行后新的状态给分别保留下来,供触发器使用。

其中要操作的当前状态保存到 old 中;操作之后的状态保存到 new 中。即,old 代表旧记录状态,new 代表新记录状态。

需要注意的是,old 和 new 都代表记录本身,只是状态不同。故,old 和 new 包含了记录数据和字段名字


【4】触发器中的new和old

首先说明一下触发器执行过程中可能遇到的几种循环:

① 触发器为 after update,执行主体为 update;

② 触发器为 after insert,执行主体为 insert;

③ 触发器为 after delete,执行主体为 delete;

和MS SQL Server 中的 INSERTED 和 DELETED 类似,MySQL 中定义了 NEW 和 OLD,用来表示触发器的所在表中,触发了触发器的那一行数据。

具体说明如下:

在 INSERT 型触发器中,NEW 用来表示将要(BEFORE)或已经(AFTER)插入的新数据;无OLD。
在 UPDATE 型触发器中,OLD 用来表示将要或已经被修改的原数据,NEW 用来表示将要或已经修改为的新数据。
在 DELETE 型触发器中,OLD 用来表示将要或已经被删除的原数据;无 NEW。
使用方法: NEW.columnName (columnName 为相应数据表某一列名);
另外,OLD 是只读的,而 NEW 则可以在触发器中使用 SET 赋值,这样不会再次触发触发器,造成循环调用。

操作中需要注意的几点如下:

① old 数据记录只能读,不能改;new 数据记录可以改,可以读;

② after 类型中,只能读,不能改;before 类型中可以读和改;

③ insert 类型中,只有 new ,无 old;

④ delete 类型中,只有 old ,无 new;

⑤ update 类型中,二者皆有;


故对 new 数据记录可以改的两种类型:

① before insert;

② before update;

示例如下:

CREATE trigger tr_puser  BEFORE insert  on p_user for each ROW
BEGIN
  set new.age = (select sum(age) from p_user);
end;

【5】删除触发器

语法格式如下:

drop trigger [数据库.]trigger_name;

示例如下:

drop TRIGGER test_mybatis.tr_puser;

多种变量赋值方式:

CREATE TRIGGER `tr_puser` AFTER INSERT ON `p_user` FOR EACH ROW BEGIN
DECLARE p_v int(11) DEFAULT 0;
DECLARE p_v1 int(11) DEFAULT 0;
  select sum(age) into p_v from p_user ;--第一种
  select sum(age)  from p_user into p_v1;--第二种
  set @user_count = p_v;--第三种
  set @user_count1 := p_v1;--第四种
  select new.age into @user_count2;
end;

触发器没有提供一个能够阻止事件发生的能力,可以采用暴力报错方式

示例如下:

CREATE TRIGGER `tr_puser` AFTER INSERT ON `p_user` FOR EACH ROW BEGIN
DECLARE p_v int(11) DEFAULT 0;
DECLARE p_v1 int(11) DEFAULT 0; 
  select sum(age) into p_v from p_user ;
  if p_v <100000 THEN
    insert into XXX values (XXX);
    -- 暴力报错 --
  end if;
end;


相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
2天前
|
关系型数据库 MySQL
Mysql基础第二十八天,使用触发器
Mysql基础第二十八天,使用触发器
18 0
Mysql基础第二十八天,使用触发器
|
2天前
|
SQL 存储 关系型数据库
【MySQL 数据库】11、学习 MySQL 中的【锁】
【MySQL 数据库】11、学习 MySQL 中的【锁】
16 0
|
2天前
|
SQL 关系型数据库 MySQL
【MySQL 数据库】4、MySQL 事务学习
【MySQL 数据库】4、MySQL 事务学习
14 0
|
25天前
|
SQL 关系型数据库 MySQL
MySQL技能完整学习列表10、数据导入和导出——1、数据导入(LOAD DATA, mysqldump)——2、数据导出(SELECT ... INTO OUTFILE, mysqldump)
MySQL技能完整学习列表10、数据导入和导出——1、数据导入(LOAD DATA, mysqldump)——2、数据导出(SELECT ... INTO OUTFILE, mysqldump)
29 0
|
2天前
|
存储 SQL 关系型数据库
【MySQL 数据库】10、MySQL 的触发器
【MySQL 数据库】10、MySQL 的触发器
8 0
|
2天前
|
存储 SQL 关系型数据库
【MySQL 数据库】6、一篇文章学习【索引知识】,提高大数据量的查询效率【文末送书】
【MySQL 数据库】6、一篇文章学习【索引知识】,提高大数据量的查询效率【文末送书】
42 0
|
4天前
|
SQL 关系型数据库 MySQL
MySQL数据库学习(小白一文掌握Mysql)
MySQL数据库学习(小白一文掌握Mysql)
21 0
|
19天前
|
SQL 存储 关系型数据库
学习mysql
学习mysql
30 5
|
25天前
|
SQL 关系型数据库 MySQL
MySQL技能完整学习列表14、应用开发和接口
MySQL技能完整学习列表14、应用开发和接口
35 0
|
25天前
|
监控 负载均衡 关系型数据库
MySQL技能完整学习列表13、MySQL高级特性——1、分区表(Partitioning)——2、复制(Replication)——3、集群(Clustering)
MySQL技能完整学习列表13、MySQL高级特性——1、分区表(Partitioning)——2、复制(Replication)——3、集群(Clustering)
39 0

热门文章

最新文章