广告位联系
返回顶部
分享到

MySQL触发器(TRIGGER)的具体使用

Mysql 来源:互联网 作者:佚名 发布时间:2024-05-23 21:48:45 人浏览
摘要

1. 触发器是什么? 定义:MySQL 的触发器和存储过程一样,都是嵌入到 MySQL 中的一段程序,是 MySQL 中管理数据的有力工具。通过对数据表的相关操作来触发、激活从而实现执行。比如当对 stu

1. 触发器是什么?

  • 定义:MySQL 的触发器和存储过程一样,都是嵌入到 MySQL 中的一段程序,是 MySQL 中管理数据的有力工具。通过对数据表的相关操作来触发、激活从而实现执行。比如当对 student 表进行操作( INSERT, DELETE 或 UPDATE)时就会激活它执行。
  • 作用:触发器与数据表关系密切,主要用于保护表中的数据。特别是当有多个表具有一定的相互联系的时候,触发器能够让不同的表保持数据的一致性、日志记录 , 数据校验等操作。

2. 触发器的优缺点

触发器的优点如下:

  • 触发器的执行是自动的,当对触发器相关表的数据做出相应的修改后立即执行。
  • 触发器可以实施比 FOREIGN KEY 约束、CHECK 约束更为复杂的检查和操作。
  • 触发器可以实现表数据的级联更改,在一定程度上保证了数据的完整性。

触发器的缺点如下:

  • 使用触发器实现的业务逻辑在出现问题时很难进行定位,特别是涉及到多个触发器的情况下,会使后期维护变得困难。
  • 大量使用触发器容易导致代码结构被打乱,增加了程序的复杂性,
  • 如果需要变动的数据量较大时,触发器的执行效率会非常低。

3. 触发器的类型

在实际使用中, MySQL 所支持的触发器有三种: INSERT 触发器、 UPDATE 触发器和 DELETE 触发器。

使用别名OLD和NEW来引用触发器中发生变化的记录内容,这与其他的数据库是相似的。现在触发器还只支持行级触发,不支持语句级触发。

触发器类型 NEW 和 OLD
INSERT 型触发器 NEW 表示将要或者已经新增的数据
UPDATE 型触发器 OLD 表示修改之前的数据 , NEW 表示将要或已经修改后的数据
DELETE 型触发器 OLD 表示将要或者已经删除的数据

3.1 INSERT触发器

在 INSERT 语句执行之前或之后响应的触发器,使用 INSERT 触发器需要注意以下几点:

更多操作在 INSERT 触发器代码内,可引用一个名为 NEW(不区分大小写)的虚拟表来访问被插入的行。

在 BEFORE INSERT 触发器中, NEW 中的值也可以被更新,即允许更改被插入的值(只要具有对应的操作权限)。

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

3.2 UPDATE触发器

在 UPDATE 语句执行之前或之后响应的触发器,使用 UPDATE 触发器需要注意以下几点:

在 UPDATE 触发器代码内,可引用一个名为 NEW(不区分大小写)的虚拟表来访问更新的值。

在 UPDATE 触发器代码内,可引用一个名为 OLD(不区分大小写)的虚拟表来访问 UPDATE 语句执行前的值。

在 BEFORE UPDATE 触发器中,NEW 中的值可能也被更新,即允许更改将要用于 UPDATE 语句中的值(只要具有对应的操作权限)。

OLD 中的值全部是只读的,不能被更新。

注意:当触发器设计对触发表自身的更新操作时,只能使用 BEFORE 类型的触发器,AFTER 类型的触发器将不被允许。

3.3 DELETE触发器

DELETE 语句执行之前或之后响应的触发器,使用 DELETE 触发器需要注意以下几点:

在 DELETE 触发器代码内,可以引用一个名为 OLD(不区分大小写)的虚拟表来访问被删除的行。

OLD 中的值全部是只读的,不能被更新。

总体来说,触发器使用的过程中,MySQL 会按照以下方式来处理错误。

  • 对于事务性表,如果触发程序失败,以及由此导致的整个语句失败,那么该语句所执行的所有更改将回滚;对于非事务性表,则不能执行此类回滚,即使语句失败,失败之前所做的任何更改依然有效。
  • 若 BEFORE 触发程序失败,则 MySQL 将不执行相应行上的操作。
  • 若在 BEFORE 或 AFTER 触发程序的执行过程中出现错误,则将导致调用触发程序的整个语句失败。
  • 仅当 BEFORE 触发程序和行操作均已被成功执行,MySQL 才会执行 AFTER 触发程序。

4. 触发器的相关语法

4.1 创建触发器

在 MySQL 5.7 中,可以使用 CREATE TRIGGER 语句创建触发器,语法格式如下:

1

2

3

CREATE <触发器名> < BEFORE | AFTER >

<INSERT | UPDATE | DELETE >

ON <表名> FOR EACH Row<触发器主体>

触发器名

触发器的名称,触发器在当前数据库中必须具有唯一的名称。如果要在某个特定数据库中创建,名称前面应该加上数据库的名称。

INSERT | UPDATE | DELETE

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

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

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

BEFORE | AFTER

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

表名

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

触发器主体

触发器动作主体,包含触发器激活时将要执行的 MySQL 语句。如果要执行多个语句,可使用 BEGIN…END 复合语句结构。

FOR EACH ROW

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

案例1:插入数据触发器

1

2

3

4

5

6

create trigger tb_user_insert_trigger

after insert on tb_user for each row

begin

    insert into user_logs(id, operation, operate_time, operate_id, operate_params) VALUES

    (null, 'insert', now(), new.id, concat('插入的数据内容为:id=',new.id,',name=',new.name, ', phone=',     NEW.phone, ', email=', NEW.email, ',profession=', NEW.profession));

end;

案例2:修改触发器

1

2

3

4

5

6

7

create trigger tb_user_update_trigger

after update on tb_user for each row

begin

    insert into user_logs(id, operation, operate_time, operate_id, operate_params) VALUES

    (null, 'update', now(), new.id,concat('更新之前的数据: id=',old.id,',name=',old.name, ', phone=',

    old.phone, ', email=', old.email, ', profession=', old.profession,' | 更新之后的数据:                  id=',new.id,',name=',new.name, ', phone=',NEW.phone, ', email=', NEW.email, ', profession=',        NEW.profession));

end;

案例3:删除触发器

1

2

3

4

5

6

7

create trigger tb_user_delete_trigger

after delete on tb_user for each row

begin

    insert into user_logs(id, operation, operate_time, operate_id, operate_params) VALUES

    (null, 'delete', now(), old.id,concat('删除之前的数据: id=',old.id,',name=',old.name, ', phone=',

    old.phone, ', email=', old.email, ', profession=', old.profession));

end;

4.2 查看触发器

在 MySQL 中,可以通过 SHOW TRIGGERS 语句来查看触发器的基本信息,语法格式如下:

1

SHOW TRIGGERS;

在这里插入图片描述

在 MySQL 中,所有触发器的信息都存在 information_schema 数据库的 triggers 表中,可以通过查询命令 SELECT 来查看,具体的语法如下:

1

SELECT * FROM information_schema.triggers WHERE trigger_name= '触发器名';

在这里插入图片描述

查看所有的触发器:

1

SELECT * FROM information_schema.triggers

4.3 删除触发器

使用 DROP TRIGGER 语句可以删除 MySQL 中已经定义的触发器。

1

DROP TRIGGER [ IF EXISTS ] [数据库名] <触发器名>


版权声明 : 本文内容来源于互联网或用户自行发布贡献,该文观点仅代表原作者本人。本站仅提供信息存储空间服务和不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权, 违法违规的内容, 请发送邮件至2530232025#qq.cn(#换@)举报,一经查实,本站将立刻删除。
原文链接 :
相关文章
  • 本站所有内容来源于互联网或用户自行发布,本站仅提供信息存储空间服务,不拥有版权,不承担法律责任。如有侵犯您的权益,请您联系站长处理!
  • Copyright © 2017-2022 F11.CN All Rights Reserved. F11站长开发者网 版权所有 | 苏ICP备2022031554号-1 | 51LA统计