首页 > 数据库 >25. 使用MySQL之使用触发器

25. 使用MySQL之使用触发器

时间：2024-11-12 21:18:50浏览次数：1  
标签：语句 25 触发器 INSERT MySQL 执行 BEFORE

1. 触发器

MySQL语句在需要时被执行，存储过程也是如此。但是，如果你想要某条语句（或某些语句）在事件发生时自动执行，怎么办呢？例如：

  • 每当增加一个顾客到某个数据库表时，都检查其电话号码格式是
    否正确，州的缩写是否为大写；

  • 每当订购一个产品时，都从库存数量中减去订购的数量；

  • 无论何时删除一行，都在某个存档表中保留一个副本。

所有这些例子的共同之处是它们都需要在某个表发生更改时自动处理。这确切地说就是触发器。触发器是MySQL响应以下任意语句而自动执行的一条MySQL语句（或位于BEGIN和END语句之间的一组语句）：

  • DELETE；

  • INSERT

  • UPDATE

其他MySQL语句不支持触发器。

2. 创建触发器

在创建触发器时，需要给出4条信息：

  • 唯一的触发器名；

  • 触发器关联的表；

  • 触发器应该响应的活动（DELETE、INSERT或UPDATE）；

  • 触发器何时执行（处理之前或之后）。

补充：

  • 保持每个数据库的触发器名唯一：

    在MySQL 5中，触发器名必须在每个表中唯一，但不是在每个数据库中唯一。这表示同一数据库中的两个表可具有相同名字的触发器。这在其他每个数据库触发器名必须唯一的DBMS中是不允许的，而且以后的MySQL版本很可能会使命名规则更为严格。因此，现在最好是在数据库范围内使用唯一的触发器名。

    可以这样理解上面这段话：

    1. 在MySQL 5中，触发器名在表级范围内是唯一的：也就是说，在同一个表中，触发器的名称不能重复，但不同表之间可以有相同名称的触发器。例如，在数据库 db1 中，你可以在表 table1 和 table2 中分别创建名为 trigger1 的触发器，系统允许这种情况，因为它们作用于不同的表。

    2. 其他DBMS要求触发器在数据库级范围内唯一：在许多其他数据库系统中（例如Oracle、SQL Server等），要求整个数据库范围内触发器名必须唯一，不能在不同表上创建相同名称的触发器。也就是说，一旦在数据库的某个表上创建了名为 trigger1 的触发器，那么在同一个数据库的其他表上不能再创建同名触发器。

触发器用CREATE TRIGGER语句创建。

比如：

create trigger newproduct after insert on products
for each row select 'Product added';

CREATE TRIGGER用来创建名为newproduct的新触发器。触发器可在一个操作发生之前或之后执行，这里给出了AFTER INSERT，所以此触发器将在INSERT语句成功执行后执行。这个触发器还指定FOR EACH ROW，因此代码对每个插入行执行。在这个例子中，文本Product added将对每个插入的行显示一次。

为了测试这个触发器，使用INSERT语句添加一行或多行到products中，你将看到对每个成功的插入，显示Product added消息。

插一句题外话

其实如果按书上的这个语句写的话，是会报错的：Error Code: 1415. Not allowed to return a result set from a trigger

改成这样就不报错了：

create trigger newproduct after insert on products
for each row select 'Product added' into @test;

原因是从MySQL5以后不支持触发器返回结果集，需要用一个变量接受结果集（此处即@test变量）。

向products表插入两条数据后，再使用select @test;查看变量@test

img

输出如下：

img

但是其实上面添加一个变量的方法感觉治标不治本，还有别的更好的方法：

可以使用 INSERT 记录日志信息：

-- 创建日志表
CREATE TABLE product_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    message VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建触发器，将日志信息插入到日志表
DELIMITER //
CREATE TRIGGER newproduct AFTER INSERT ON products 
FOR EACH ROW 
BEGIN
    INSERT INTO product_log (message) VALUES ('Product added');
END //
DELIMITER ;

这样，每当在 products 表中插入一条新记录，触发器就会自动将“Product added”消息插入到 product_log 表中。

回归正题

注意：

  • 仅支持表：

    只有表才支持触发器，视图不支持（临时表也不支持）。

触发器按每个表每个事件每次地定义，每个表每个事件每次只允许一个触发器。因此，每个表最多支持6个触发器（每条INSERT、UPDATE和DELETE的之前和之后）。单一触发器不能与多个事件或多个表关联，所以，如果你需要一个对INSERT和UPDATE操作执行的触发器，则应该定义两个触发器。

补充：

  • 触发器失败：

    如果BEFORE触发器失败，则MySQL将不执行请求的操作。此外，如果BEFORE触发器或语句本身失败，MySQL将不执行AFTER触发器（如果有的话）。

    插句题外话

    这段话的意思是：在MySQL中，如果 BEFORE 触发器执行失败，那么MySQL会中止该操作，即不再执行实际的数据库操作（例如 INSERT、UPDATE 或 DELETE），而且 AFTER 触发器（如果存在）也不会被触发。这是MySQL的一个重要机制，用于确保数据的完整性和一致性。

    具体解释如下：

    1. BEFORE 触发器失败时：

    BEFORE 触发器是在指定操作（如 INSERT、UPDATE 或 DELETE）之前执行的。如果 BEFORE 触发器遇到错误或执行失败，MySQL会取消该操作，因此不会执行该操作本身。例如，如果一个 INSERT 操作的 BEFORE INSERT 触发器失败，那么这条 INSERT 语句就不会被执行。同时，任何相关的AFTER 触发器也不会被执行，因为 AFTER 触发器只有在请求的操作成功执行后才会被触发。

    1. 语句本身失败时：

    即使 BEFORE 触发器成功执行，但如果实际的数据库操作（例如 INSERT）本身因某种原因失败（例如违反了唯一性约束），那么 AFTER 触发器也不会执行。因为 AFTER 触发器是基于操作成功执行后的情况来触发的。

    1. 举个例子

    假设有一张 products 表，并为 INSERT 操作定义了 BEFORE INSERT 和 AFTER INSERT 触发器。

    DELIMITER //

-- BEFORE INSERT 触发器
CREATE TRIGGER before_insert_product BEFORE INSERT ON products
FOR EACH ROW
BEGIN
    -- 如果某些条件不满足，触发器可以引发错误
    IF NEW.price <= 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Price must be greater than zero';
    END IF;
END//

-- AFTER INSERT 触发器
CREATE TRIGGER after_insert_product AFTER INSERT ON products
FOR EACH ROW
BEGIN
    -- 记录日志或执行其他操作
    INSERT INTO product_log (message) VALUES ('Product added');
END//

DELIMITER ;

    在这个例子中：

    • 如果在 BEFORE INSERT 触发器中发现 price 小于等于 0，那么触发器会引发错误并停止执行，这样 INSERT 操作就不会完成，也不会触发AFTER INSERT 触发器。

    • 只有当 BEFORE INSERT 成功且 INSERT 语句本身没有出错时，AFTER INSERT 触发器才会执行。

    通过这种机制，MySQL确保了只有在所有操作都顺利完成的情况下才会触发 AFTER 触发器，避免在部分失败的操作中执行额外的后续逻辑。

3. 删除触发器

为了删除一个触发器，可使用DROP TRIGGER语句。

比如：

drop trigger newproduct;

触发器不能更新或覆盖。为了修改一个触发器，必须先删除它，然后再重新创建。

4. 使用触发器

在有了前面的基础知识后，我们现在来看所支持的每种触发器类型以及它们的差别。

4.1 INSERT 触发器

INSERT触发器在INSERT语句执行之前或之后执行。需要知道以下几点：

  • 在INSERT触发器代码内，可引用一个名为NEW的虚拟表，访问被插入的行；

  • 在BEFORE INSERT触发器中，NEW中的值也可以被更新（允许更改被插入的值）；

  • 对于AUTO_INCREMENT列，NEW在INSERT执行之前包含0，在INSERT执行之后包含新的自动生成值。

下面举一个例子（一个实际有用的例子）。

AUTO_INCREMENT列具有MySQL自动赋予的值。第21章建议了几种确定新生成值的方法，但下面是一种更好的方法：

create trigger neworder after insert on orders
for each row select new.order_num;

插句题外话

如果按上面那样写的话，会报大大的错

img

可以尝试写成这样：

create trigger neworder after insert on orders
for each row select new.order_num into @neworder_num;

为什么会报错呢？ 为什么写成这样不报错呢？

  • 在 MySQL5 后，触发器（trigger）不允许返回结果集（即执行 SELECT 语句直接输出数据）。

  • 在 MySQL 中，SELECT ... INTO @变量名 和 SELECT ... 的行为有所不同：

    • SELECT ... INTO @变量名：这种语法将查询结果存储到一个用户定义的变量中，不会返回结果集。这在触发器或存储过程内部是允许的，因为它不会将数据返回到客户端，只是在内部操作。

    • SELECT ...：直接执行 SELECT 查询会返回一个结果集给客户端，触发器内部不允许这样做，因此会报错 1415。

    通过使用 SELECT ... INTO @变量名，可以将值存储在变量中进行后续处理，而不触发返回结果集的限制。这也是为什么加上 INTO 后，代码在触发器中可以正常运行。

  • 想了解一下@变量名的话可以看下这篇博客 MySQL中的@变量名

回归正题

此代码创建一个名为neworder的触发器，它按照AFTER INSERT ON orders执行。在插入一个新订单到orders表时，MySQL生成一个新订单号并保存到order_num中。触发器从NEW.order_num取得这个值并返回它。

此触发器必须按照AFTER INSERT执行，因为在BEFORE INSERT语句执行之前，新order_num还没有生成。

对于orders的每次插入使用这个触发器将总是返回新的订单号。

为测试这个触发器，试着插入一下新行，如下所示：

insert into orders(order_date, cust_id)
values(now(), 10001);

orders 包 含 3 个列。 order_date 和 cust_id 必须给出，order_num由MySQL自动生成，而现在order_num还自动被返回。

img

补充：

  • BEFORE或AFTER？

    通常，将BEFORE用于数据验证和净化（目的是保证插入表中的数据确实是需要的数据）。本提示也适用于UPDATE触发器。

4.2 DELETE 触发器

DELETE触发器在DELETE语句执行之前或之后执行。需要知道以下两点：

  • 在DELETE触发器代码内，可以引用一个名为OLD的虚拟表，访问被删除的行；

  • OLD中的值全都是只读的，不能更新。

下面的例子演示使用OLD保存将要被删除的行到一个存档表中：

CREATE TABLE archive_orders
(
  order_num  int      NOT NULL AUTO_INCREMENT,
  order_date datetime NOT NULL ,
  cust_id    int      NOT NULL ,
  PRIMARY KEY (order_num)
);

delimiter //
create trigger deleteorder before delete on orders
for each row
begin
	insert into archive_orders(order_num, order_date, cust_id)
    values(old.order_num, old.order_date, old.cust_id);
end //
delimiter ;

在任意订单被删除前将执行此触发器。它使用一条INSERT语句将OLD中的值（要被删除的订单）保存到一个名为archive_ orders的存档表中（为实际使用这个例子，需要用与orders相同的列创建一个名为archive_orders的表）。

使用BEFORE DELETE触发器的优点（相对于AFTER DELETE触发器来说）为，如果由于某种原因，订单不能存档，DELETE本身将被放弃。

补充：

  • 多语句触发器：

    正如所见，触发器deleteorder使用BEGIN和END语句标记触发器体。这在此例子中并不是必需的，不过也没有害处。使用BEGIN END块的好处是触发器能容纳多条SQL语句（在BEGIN END块中一条挨着一条）。

插句题外话

给出我的实践过程图：

未对orders表执行删除订单操作时，orders表的样子：

img

对orders表执行删除订单操作后，查看archive_ orders表和orders表：

img

img

4.3 UPDATE 触发器

UPDATE触发器在UPDATE语句执行之前或之后执行。需要知道以下几点：

  • 在UPDATE触发器代码中，可以引用一个名为OLD的虚拟表访问以前（UPDATE语句前）的值，引用一个名为NEW的虚拟表访问新更新的值；

  • 在BEFORE UPDATE触发器中，NEW中的值可能也被更新（允许更改将要用于UPDATE语句中的值）；

  • OLD中的值全都是只读的，不能更新。

下面的例子保证州名缩写总是大写（不管UPDATE语句中给出的是大写还是小写）：

create trigger updatevendor before update on vendors
for each row set new.vend_state = upper(new.vend_state);

显然，任何数据净化都需要在UPDATE语句之前进行，就像这个例子中一样。每次更新一个行时，NEW.vend_state中的值（将用来更新表行的值）都用Upper(NEW.vend_state)替换。

4.4 关于触发器的进一步介绍

我们再介绍一些使用触发器时需要记住的重点。

  • 与其他DBMS相比，MySQL 5中支持的触发器相当初级。未来的MySQL版本中有一些改进和增强触发器支持的计划。

  • 创建触发器可能需要特殊的安全访问权限，但是，触发器的执行是自动的。如果INSERT、UPDATE或DELETE语句能够执行，则相关的触发器也能执行。

  • 应该用触发器来保证数据的一致性（大小写、格式等）。在触发器中执行这种类型的处理的优点是它总是进行这种处理，而且是透明地进行，与客户机应用无关。

  • 触发器的一种非常有意义的使用是创建审计跟踪。使用触发器，把更改（如果需要，甚至还有之前和之后的状态）记录到另一个表非常容易。

  • 遗憾的是，MySQL触发器中不支持CALL语句。这表示不能从触发器内调用存储过程。所需的存储过程代码需要复制到触发器内。

标签：语句,25,触发器,INSERT,MySQL,执行,BEFORE
From： https://www.cnblogs.com/hisun9/p/18542682

相关文章

  • Mysql表分区实操
    创建分区表createtableuser(idint(11)notnull,namevarchar(32)notnull)partitionbyrange(id)(partitionp0valueslessthan(10),partitionp1valueslessthan(20),partitionp2valueslessthan(30),partitionp3valueslessthanmaxvalue)分区表数据......
  • Mysql大表处理方案：分区详解
    前言在我们日常处理海量数据的过程中，如何有效管理和优化数据库一直是一个既重要又具有挑战性的问题，较为通用的做法就是分区、分表、分库了。那我们应该如何选择这几种方式呢？下面本人记录以下几种方式的优劣，希望能够帮助到大家。首先分区:数据库中分区是将表或索引的数据划分成......
  • mysql修改/删除
    mysql增删改查中的改使用update表名set列名条件where；  matched是当前表有几行，changed是执行了几行，warnings是警告删除deleteform表名where条件；删除表里的某行信息 deletefrom表名；是删除整张表数据 ......
  • mysql创建表
    创建表创建表要先选中数据库名createtable表名（列名类型）来进行创建，列名可以存在多个，表名和列名不可以一样，想要一样的话就要使用反引号把表名和列名引起来列名在前面，类型在后面查看所有表showtables查看当前数据库所有表 查看指定表的结构desc表名；把表的特征描述出......
  • mysql查询
    全列查询使用select*from表名；把表中所有的行和列全部都查询出来  指定列查询select列名，列名from表名； 查询字段为表达式可以边查询边计算，查询的时候把列名构成的表达式，把这一列中的所有行都带入到表达式中进行运算，且操作的数据不会在更改原来的数据select列名......
  • Linux二进制安装 MySQL 5.7.44
    1.下载MySQL5.7.44的二进制文件首先，访问MySQL官网下载页面，选择适合你操作系统架构（如x86_64）的二进制安装包。访问官网：MySQLDownloads在页面中选择适合你系统的版本（比如Linux的 tar 包）。下载Mysql  https://download.csdn.net/download/m0_58223765/89975252例......
  • 【PAT_Python解】1125 子串与子列
    原题链接：PTA|程序设计类实验辅助教学平台Tips：以下Python代码仅个人理解，非最优算法，仅供参考！多学习其他大佬的AC代码！测试点5超时：defmin_window_substring(s,p):len1=len(s)len2=len(p)mixn=0min_length=len1+1#设置为一个较大的值......
  • CES2025展会媒体发布现场邀约采访计划来啦!海外科技电子媒体报纸新闻宣传方案
    【本篇由言同数字全球媒体投放原创】一、CES2025展会概述CES（国际消费电子展）是全球规模最大、最具影响力的消费电子展会之一，由美国消费技术协会（CTA）主办。每年1月，CES吸引来自全球各地的行业领袖、创新企业、媒体和技术爱好者，共同见证科技产业的最新突破。CES2025将于2025......
  • 详解连接 MySQL8.4 出现 Client does not support authentication protocol requested
    文章目录项目场景问题描述原因分析解决方案方案一（不推荐）方案二（推荐）总结项目场景在开发过程中，我们在Node.js项目中使用MySQL数据库，并且通过typeorm和mysql库进行数据库连接。然而，在项目启动时，遇到了数据库连接失败的问题，导致项目无法正常运行。问题描述......
  • springboot高校心理测评系统-计算机毕业设计源码25173
    目 录第1章 引 言1.1 选题背景1.2 研究现状1.3 论文结构安排第2章 系统的需求分析2.1 系统可行性分析2.1.1 技术方面可行性分析2.1.2 经济方面可行性分析2.1.3 法律方面可行性分析2.1.4 操作方面可行性分析2.2 系统功能需求分析2.......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99