但行好事
莫论前程❤

MySQL数据库系列(17)–触发器使用

Q:什么是触发器?

A: 触发器是与表有关的数据库对象,在满足定义条件时触发,并执行触发器中定义的语句集合。

img

!!尽量少使用触发器,不建议使用。

触发器尽量少的使用,因为不管如何,它还是很消耗资源,如果使用的话要谨慎的使用,确定它是非常高效的:触发器是针对每一行的;对增删改非常频繁的表上切记不要使用触发器,因为它会非常消耗资源。

触发器:在指定表上,(insert(插入)、update(跟新)、delete(删除))事件动作,触发(After(之后)时机,Before(之前)),执行指定的一群或一个sql语句。
举例解释:就是在delete删除之前(before)或之后(after)执行指定一个或一群sql语句
MySQL支持触发器:在Mysql 5.0版本以上。在doc命令查看mysql版本命令(mysql --version)

在mysql中创建

触发一条sql语句(单一执行语句)
CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW
trigger_stmt

#### 例子:
CREATE TRIGGER trig1 AFTER INSERT ON work FOR EACH ROW
INSERT INTO time VALUES(NOW());
================================================================================

触发多条sql语句(多执行语句)
-- 创建触发器基本语法
delimiter 自定义符号 -- 临时修改语句结束符,在后续语句中只有遇到自定义符号才会结束语句
create trigger + 触发器名称 + 触发器时间 + 事件类型 on 表名 for each row
begin -- 代表触发器内容开始
    -- 触发器内容主体,每行用分号结尾
end -- 代表触发器内容结束
自定义符号 -- 用于结束语句
delimiter ; -- 恢复语句结束符

#### 例子:
delimiter $$ 
create trigger after_order_new after insert on orders for each row
begin   
    -- 触发器内容主体,每行用分号结尾
    update goods set inventory = inventory - NEW.goods_number where id = NEW.goods_id;
end 
$$  
delimiter ;     
  • trigger_name : 触发器名称,用户自行指定
  • trigger_time: 触发时机,取值BEFORE(之前)、AFTER(之后)
  • trigger_event : 出发事件,INSERTUPDATEDELETE。(插入、更新、删除).触发器只能由一个事件调用。要定义由多个事件调用的触发器,必须定义多个触发器,每个事件一个触发器。
  • 触发器必须与特定表关联。没有表触发器将不存在,所以必须在ON关键字之后指定表名。
  • tbl_name : 需要建立触发器的表名。
  • trigger_stmt : 触发程序体,可以是一条SQL语句或是BEGINEND包含的索条语句,即将SQL语句放在BEGINEND块之间。这是定义触发器逻辑的位置。
  • 由上面,可以知道MYSQL可以创建6种类型的触发器。
(BEFORE INSERT、BEFORE UPDATE、BEFORE DELETE)
(AFTER INSERT、AFTER UPDATE、AFTER DELETE)
  • 并且一张表上不能创建两个相同类型的触发器,因此一张表上面最多能创建6种类型的触发器。

trigger_event详解

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

LOAD DATA:

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

REPLACE:

语句和INSERT语句很像,只是在表中有primary keyunique索引时,如果插入的数据和原来的primary keyunique索引一致时,会先删除原来的数据,然后增加一条新的数据,就是REPLACE语句有时候等价一条INSERT语句,有时候等价一条DELETE语句加上一条INSERT语句

BEGIN…END详解

在Mysql中,BEGIN…END语句的语法为:

BEGIN
[statement_list]
END

statement_list:使用方法

statement_list 代表一个或多个语句的列表,列表内的每条语句都必须用分号(;)来结尾(默认值)
我们可以使用DELIMITERE指定自定义的定界符。
DELIMITER new_delemiter
new_delemiter 可以设为1个或多个长度的符号,默认的是分号(;),我们可以把它修改为其他符号,如\$:DELIMITER $(注意:我们修改了定界符使用完了,记得修改回来DELIMITER ;)

参考代码

img

Mysql中使用DECLARE来定义一句局部变量,该变量只能在BEGIN…END复合语句中使用,并且应该定义在复合语句的开头。

DECLARE var_name[,...] type [DEFAULT value]
  • var_name:变量名称,同 SQL 语句一样,变量名不区分大小写

  • type:数据类型MySQL 支持的任何数据类型,可以同时定义多个同类型的变量,用逗号隔开

  • DEFAULT:变量初始值为 NULL,如果需要,可以使用 DEFAULT 子句提供默认值,值可以被指定为一个表达式

对变量赋值采用SET语句

SET var_name = expr [,var_name = expr] ...

NEW与OLD关键字

该关键字,表示触发了触发器的那一行数据。

  • INSERT触发器中, NEW用来表示将要(BEFORE)或已经(AFTER)插入的新数据。
  • UPDATE触发器中,OLD用来表示将要或已经被修改的原数据,NEW用来表示将要或已经修改为的新数据。
  • DELETE触发器中,OLD用来表示将要或已经被删除的原数据。

使用方法NEW.columnName(columnName为相应数据表某一列名) 另外,OLD 是只读的,而 NEW 则可以在触发器中使用 SET 赋值,这样不会再次触发触发器,造成循环调用(如每插入一个学生前,都在其学号前加“2013”)

查看触发器

  • 查看触发器就和查看数据库/表格
show databases/tables;
  • 查看触发器
SHOW TRIGGERS [FROM schema_name];

这里的schema_name表示的是表名称。

删除触发器

  • 和删除数据表、删除表格一样,语法如下:
DROP TRIGGER IF EXISTS trigger_name

如果不需要某个触发器时一定要将这个触发器删除,以免造成意外操作,这很关键。

触发器的执行顺序

触发器建立的一般是InnoDB数据库,上面要使用的表也是要事务支持的。

  • 如果BEFORE触发器执行失败,SQL无法正确执行。
  • SQL执行失败时,AFTER型触发器不会触发。
  • AFTER类型的触发器执行失败,SQL会回滚。

案例演示:

我们先来创建两张表,分别为商品表goods和订单表orders,SQL 语句如下:

-- 创建商品表
create table goods(
    id int primary key auto_increment,
    name varchar(20) not null,
    price decimal(10, 2) default 0,
    inventory int comment '商品库存量'
)charset utf8;

-- 插入两条数据
insert into goods values(null, 'iPhone8', 5088, 1000), (null, 'iPhoneX', 8088, 1000);

-- 创建订单表
create table orders(
    id int primary key auto_increment,
    goods_id int not null,
    goods_number int default 1
)charset utf8;

img

接下来,执行如下 SQL 语句,创建触发器:

-- 创建触发器
delimiter $$ -- 临时修改语句结束符
create trigger after_order after insert on orders for each row
begin -- 触发器内容开始
    -- 触发器内容主体,每行用分号结尾
    update goods set inventory = inventory - 1 where id = 1;
end -- 触发器内容结束
$$ -- 结束语句
delimiter ; -- 恢复语句结束符

img

查询触发器

查询所有触发器或模糊匹配,

  • 基本语法show triggers + [like 'pattern'];

执行如下 SQL 语句,进行测试:

查询所有触发器,\G 表示旋转
show triggers\G;

img

当然,我们也可以查询创建触发器的语句

  • 基本语法show create trigger + 触发器名称;

执行如下 SQL 语句,进行测试:

-- 查询触发器创建语句,\G 表示旋转
show create trigger after_order\G;

img

此外,所有的触发器都会被系统保持到information_schema.triggers这张表中,执行如下 SQL,进行测试:

-- 查询触发器,\G 表示旋转
select * from information_schema.triggers\G;

img

使用触发器

实际上,触发器不是我们手动触发的,而是在某种情况发生的时候自动触发,例如我们上面创建的after_order触发器,当我们insert订单表的时候,该触发器自动执行。执行如下 SQL 语句,进行测试:

img

修改触发器 & 删除触发器

触发器不能修改,只能删除。因此,当我们需要修改触发器的时候,唯一的方法就是:先删除,后新增。

  • 基本语法drop trigger + 触发器名称;
-- 删除触发器
drop trigger after_order;
-- 查询触发器
show triggers;

img

触发器记录

触发器记录:无论触发器是否触发,只要当某种操作准备执行,系统就会将当前操作的记录的当前状态和即将执行之后的状态分别记录下来,供触发器使用。其中,当前状态被保存到old中,操作之后的状态被保存到new中。至于old和new是什么鬼?不知道大家是否还记得查看表information_schema.triggers的时候,标红的两个字段:

  • ACTION_REFERENCE_OLD_ROW:OLD
  • ACTION_REFERENCE_NEW_ROW:NEW

其中,

OLD,代表是旧记录,也就是当前记录的状态,插入时没有OLD;
NEW,代表是新记录,也就是假设操作发生之后记录的状态,删除时没有NEW。
无论OLD还是 NEW,都代表记录本身,而且任何一条记录除了有数据,还有字段名。因此,使用OLD和 NEW的方法就是:

  • 基本语法OLD/NEW + . + 字段名

在这里,我们就能够通过触发器记录解决刚才after_order触发器的问题了。依次执行如下 SQL 语句,进行测试:

-- 创建新触发器
delimiter $$ -- 临时修改语句结束符
create trigger after_order_new after insert on orders for each row
begin -- 触发器内容开始
    -- 触发器内容主体,每行用分号结尾
    update goods set inventory = inventory - NEW.goods_number where id = NEW.goods_id;
end -- 触发器内容结束
$$ -- 结束语句
delimiter ; -- 恢复语句结束符

-- 查看新触发器
show triggers\G;

img

-- 查看商品表
select * from goods;
-- 查看订单表
select * from orders;
-- 插入订单表
insert into orders values(null, 2, 10);
-- 查看订单表
select * from orders;
-- 查看商品表
select * from goods;

img

如上图所示,显然after_order_new触发器按我们预期那样正确的工作啦!

温馨提示:符号[]括起来的内容,表示可选项;符号+,则表示连接的意思。

赞(0) 打赏
未经允许不得转载:刘鹏博客 » MySQL数据库系列(17)–触发器使用
分享到: 更多 (0)

评论 抢沙发

评论前必须登录!

 

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏