MySQL学习笔记(九): 触发器

MySQL中的触发器(TRIGGER)

触发器 是一种特殊的存储过程，它在插入，删除或修改特定表中的数据时触发执行，它比数据库本身标准的功能有更精细和更复杂的数据控制能力。它在修改我们的特定表的时候，它才会触发，它不是用户所调用的，而是数据库自己去执行的。

注意:

1. 它不能直接被调用，是由数据库主动去执行。

1. 触发器的创建

在 MySQL 5.7 中，可以使用 CREATE TRIGGER 语句创建触发器。

基本语法:

CREATE TRIGGER <触发器名> 
<BEFORE | AFTER> <INSERT | UPDATE | DELETE> ON <表名> 
FOR EACH ROW
BEGIN
<触发器主体>
END;

注意:

• 只有表支持触发器, 视图不支持(临时表也不支持)
  触发器创建语法的要素:

1. 触发器名

   触发器的名称，触发器在当前数据库中必须具有唯一的名称。

2. 触发时间

   触发时间分为 BEFORE 和 AFTER，触发器被触发的时刻，表示触发器是在激活它的语句之前或之后触发。若希望验证新数据是否满足条件，则使用 BEFORE 选项；若希望在激活触发器的语句执行之后完成几个或更多的改变，则通常使用 AFTER 选项。

3. 触发事件

   触发事件，用于指定激活触发器的语句的种类。

   注意：三种触发器的执行时间如下。

   • INSERT：将新行插入表时激活触发器。例如，INSERT 的 BEFORE 触发器不仅能被 MySQL 的 INSERT 语句激活，也能被 LOAD DATA 语句激活。
   • DELETE： 从表中删除某一行数据时激活触发器，例如 DELETE 和 REPLACE 语句。
   • UPDATE：更改表中某一行数据时激活触发器，例如 UPDATE 语句。

4. 监视地点

   与触发器相关联的表名，此表必须是永久性表，不能将触发器与临时表或视图关联起来。在该表上触发事件发生时才会激活触发器。同一个表不能拥有两个具有相同触发时刻和事件的触发器. 例如，对于一张数据表，不能同时有两个 BEFORE UPDATE 触发器，但可以有一个 BEFORE UPDATE 触发器和一个 BEFORE INSERT 触发器，或一个 BEFORE UPDATE 触发器和一个 AFTER UPDATE 触发器。

5. FOR EACH ROW

   一般是指行级触发，对于受触发事件影响的每一行都要激活触发器的动作。例如，使用 INSERT 语句向某个表中插入多行数据时，触发器会对每一行数据的插入都执行相应的触发器动作。

6. BEGIN和 END

   BEGIN 和 END 相当于一对花括号, 用来表示主体的开始和结束

7. 触发器主体

   触发器动作主体，包含触发器激活时将要执行的 MySQL 语句。

例如:

# 修改命令结束标识符
delimiter $

# 添加一个触发器: 在新增一条订单时, 自动在商品表中扣除相应的库存
create trigger tri_1
after insert on `order`
for each row
begin
update commodity set c_num=c_num-new.o_num where c_id=new.o_cid;
end$
delimiter ;

# 添加一个触发器: 在删除一条订单时, 自动在商品表中加上相应的库存
create trigger tri_2
after delete on `order`
for each row
begin
update commodity set c_num=c_num+old.o_num where c_id=old.o_cid;
end$

# 添加一个触发器: 在更新订单的商品数量时, 自动在商品表中修改相应的库存
create trigger tri_3
after update on `order`
for each row
begin
update commodity set c_num=c_num+old.o_num-new.o_num where c_id=new.o_cid;
end$

# 修改命令结束标识符
delimiter ;

为什么要使用 delimiter:

begin end 相当于一对花括号, 其中的内容为要执行的SQL语句, 每句SQL语句之间用分号隔开. 但是在命令行中分号是SQL语句结束的标志, 一旦遇到分号就会执行. 因此在写创建触发器前, 要修改命令结束标识符, 否则将无法创建, 最后在创建完成后也将命令结束标识符修改回来.

在上述创建语句中, 还使用了 new , old 两个关键字:

触发器针对的是数据库中的每一行记录，每行数据在操作前后都会有一个对应的状态.当触发事件发生后时, 触发器会将触发之前的状态保存到 old 关键字中，将触发后的状态保存到 new 中, 这样就可以获取被监视的表中的数据了.

需要注意的是，old 和 new 不是所有触发器都有

触发器类型	含义
INSERT型触发器	只有 new，new 表示将要(插入前)或者已经增加(插入后)的数据
UPDATE型触发器	既有 old 也有 new，old 表示更新之前的数据，new 表示更新之后的数据
DELETE型触发器	只有 old，old 表示将要(删除前)或者已经被删除(删除后)的数据

2. 触发器的查看

• 查看所有触发器

  show triggers;
  

• 查看触发器的创建语句

  show create trigger 触发器名;
  

3. 触发器的删除

语法格式如下:

drop trigger 触发器名;

4. 触发器的修改

MySQL中修改触发器可以通过删除原触发器，再以相同的名称创建新的触发器。