认真学习MySQL 中的触发器【1】触发器定义【2】创建触发器【3】查看触发器【4】触发器中的new和old【5】删除触发器

78 阅读 0 评论 52 点赞

我是靠谱客的博主端庄路灯，最近开发中收集的这篇文章主要介绍认真学习MySQL 中的触发器【1】触发器定义【2】创建触发器【3】查看触发器【4】触发器中的new和old【5】删除触发器，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

【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;

这里需要说明一点如果是在DOS中，不能这样子创建，需要修改语句结束符。

delimiter 自定义符号;

--后续代码中只有碰到自定义符号才算语句结束。

示例如下：

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;

这里写图片描述

查看触发器创建语句 语法格式如下：

show create trigger 触发器名字;

示例如下：

show create trigger tr_puser;

这里写图片描述

触发器记录

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

其中要操作的当前状态保存到 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;