MySQL触发器trigger的使用

目录
  • 一、触发器简介
    • 1、什么是触发器?
    • 2、触发器的特性
  • 二、、创建触发器
    • 1、创建只有一个执行语句的触发器
    • 2、创建有多个执行语句的触发器
    • 3、NEW与OLD详解
  • 三、查看触发器
    • 1、SHOW TRIGGERS语句查看触发器信息
    • 2、在information_schema.triggers表中查看触发器信息
  • 四、删除触发器

一、触发器简介

1、什么是触发器?

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

2、触发器的特性

  • begin end体,begin end;之间的语句可以写的简单或者复杂
  • 什么条件会触发:I、D、U
  • 什么时候触发:在增删改前或者后
  • 触发频率:针对每一行执行
  • 触发器定义在表上,附着在表上。

也就是由事件来触发某个操作,事件包括INSERT语句,UPDATE语句和DELETE语句;可以协助应用在数据库端确保数据的完整性。

注意:cannot associate a trigger with a TEMPORARY table or a view.

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

假设触发器触发每次执行1s,insert table 500条数据,那么就需要触发500次触发器,光是触发器执行的时间就花费了500s,而insert 500条数据一共是1s,那么这个insert的效率就非常低了。因此我们特别需要注意的一点是触发器的begin end;之间的语句的执行效率一定要高,资源消耗要小。

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

二、、创建触发器

CREATE
    [DEFINER = { user | CURRENT_USER }]
TRIGGER trigger_name
trigger_time trigger_event
ON tbl_name FOR EACH ROW
  [trigger_order]
trigger_body

trigger_time: { BEFORE | AFTER }

trigger_event: { INSERT | UPDATE | DELETE }

trigger_order: { FOLLOWS | PRECEDES } other_trigger_name

> BEFOREAFTER参数指定了触发执行的时间,在事件之前或是之后。

> FOR EACH ROW表示任何一条记录上的操作满足触发事件都会触发该触发器,也就是说触发器的触发频率是针对每一行数据触发一次。

> tigger_event详解:

  ①INSERT型触发器:插入某一行时激活触发器,可能通过INSERTLOAD DATAREPLACE 语句触发(LOAD DAT语句用于将一个文件装入到一个数据表中,相当与一系列的INSERT操作);

  ②UPDATE型触发器:更改某一行时激活触发器,可能通过UPDATE语句触发;

  ③DELETE型触发器:删除某一行时激活触发器,可能通过DELETEREPLACE语句触发。

> trigger_orderMySQL5.7之后的一个功能,用于定义多个触发器,使用follows(尾随)或precedes(在…之先)来选择触发器执行的先后顺序。

1、创建只有一个执行语句的触发器

CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件 ON 表名 FOR EACH ROW 执行语句;

例1:创建了一个名为trig1的触发器,一旦在work表中有插入动作,就会自动往time表里插入当前时间

mysql> CREATE TRIGGER trig1 AFTER INSERT
    -> ON work FOR EACH ROW
    -> INSERT INTO time VALUES(NOW());

2、创建有多个执行语句的触发器

CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件

ON 表名 FOR EACH ROW

BEGIN

执行语句列表

END;

例2:定义一个触发器,一旦有满足条件的删除操作,就会执行BEGINEND中的语句

mysql> DELIMITER ||
mysql> CREATE TRIGGER trig2 BEFORE DELETE
    -> ON work FOR EACH ROW
    -> BEGIN
    ->   INSERT INTO time VALUES(NOW());
    ->   INSERT INTO time VALUES(NOW());
    -> END||
mysql> DELIMITER ;
 

3、NEW与OLD详解

MySQL 中定义了 NEW OLD,用来表示触发器的所在表中,触发了触发器的那一行数据,来引用触发器中发生变化的记录内容,具体地:

在INSERT型触发器中,NEW用来表示将要(BEFORE)或已经(AFTER)插入的新数据;

在UPDATE型触发器中OLD用来表示将要或已经被修改的原数据,NEW用来表示将要或已经修改为的新数据;

在DELETE型触发器中OLD用来表示将要或已经被删除的原数据;

使用方法:

  NEW.columnName (columnName为相应数据表某一列名)

另外,OLD是只读的,而NEW则可以在触发器中使用 SET 赋值,这样不会再次触发触发器,造成循环调用(如每插入一个学生前,都在其学号前加“2013”)。

例3:

mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
mysql> INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00);

mysql> delimiter $$
mysql> CREATE TRIGGER upd_check BEFORE UPDATE ON account
    -> FOR EACH ROW
    -> BEGIN
    ->   IF NEW.amount < 0 THEN
    ->     SET NEW.amount = 0;
    ->   ELSEIF NEW.amount > 100 THEN
    ->     SET NEW.amount = 100;
    ->   END IF;
    -> END$$
mysql> delimiter ;

mysql> update account set amount=-10 where acct_num=137;

mysql> select * from account;
+----------+---------+
| acct_num | amount  |
+----------+---------+
|      137 |    0.00 |
|      141 | 1937.50 |
|       97 | -100.00 |
+----------+---------+

mysql> update account set amount=200 where acct_num=137;

mysql> select * from account;
+----------+---------+
| acct_num | amount  |
+----------+---------+
|      137 |  100.00 |
|      141 | 1937.50 |
|       97 | -100.00 |
+----------+---------+

三、查看触发器

1、SHOW TRIGGERS语句查看触发器信息

mysql> SHOW TRIGGERS\G;

……

结果,显示所有触发器的基本信息;无法查询指定的触发器。

2、在information_schema.triggers表中查看触发器信息

mysql> SELECT * FROM information_schema.triggers\G

……

结果,显示所有触发器的详细信息;同时,该方法可以查询制定触发器的详细信息。

mysql> select * from information_schema.triggers
    -> where trigger_name='upd_check'\G;
Tips:

所有触发器信息都存储在information_schema数据库下的triggers表中,可以使用SELECT语句查询,如果触发器信息过多,最好通过TRIGGER_NAME字段指定查询。

四、删除触发器

DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name

删除触发器之后最好使用上面的方法查看一遍;同时,也可以使用database.trig来指定某个数据库中的触发器。

Tips:

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

到此这篇关于MySQL触发器trigger的使用的文章就介绍到这了,更多相关MySQL触发器trigger使用内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

(0)

相关推荐

  • mysql触发器(Trigger)简明总结和使用实例

    一,什么触发器 1,个人理解触发器,从字面来理解,一触即发的一个器,简称触发器(哈哈,个人理解),举个例子吧,好比天黑了,你开灯了,你看到东西了.你放炮仗,点燃了,一会就炸了.2,官方定义触发器(trigger)是个特殊的存储过程,它的执行不是由程序调用,也不是手工启动,而是由事件来触发,比如当对一个表进行操作( insert,delete, update)时就会激活它执行.触发器经常用于加强数据的完整性约束和业务规则等. 触发器可以从 DBA_TRIGGERS ,USER_TRIGGERS 数

  • mysql日志触发器实现代码

    sql语句 DROP TRIGGER IF EXISTS sys_menu_edit; CREATE TRIGGER sys_menu_edit BEFORE UPDATE ON sys_menu FOR EACH ROW BEGIN INSERT INTO `g4m`.`sys_log` ( `table_name`, `val_id`, `data_json` ) VALUES ( 'sys_menu', old.id, CONCAT( "{", CONCAT_WS( ',', C

  • mysql触发器trigger实例详解

    MySQL好像从5.0.2版本就开始支持触发器的功能了,本次博客就来介绍一下触发器,首先还是谈下概念性的东西吧: 什么是触发器 触发器是与表有关的数据库对象,在满足定义条件时触发,并执行触发器中定义的语句集合.触发器的这种特性可以协助应用在数据库端确保数据的完整性. 举个例子,比如你现在有两个表[用户表]和[日志表],当一个用户被创建的时候,就需要在日志表中插入创建的log日志,如果在不使用触发器的情况下,你需要编写程序语言逻辑才能实现,但是如果你定义了一个触发器,触发器的作用就是当你在用户表中

  • mysql触发器之触发器的增删改查操作示例

    本文实例讲述了mysql触发器之触发器的增删改查操作.分享给大家供大家参考,具体如下: 我们在创建触发器后,可以在包含触发器定义文件的数据文件夹中显示其定义.触发器作为纯文本文件存储在以下数据库文件夹中: /data_folder/database_name/table_name.trg 我们还可通过查询information_schema数据库中的triggers表来显示触发器,如下所示: SELECT * FROM information_schema.triggers WHERE trig

  • 一篇文章带你深入了解Mysql触发器

    目录 1.对SC表进行插入或修改时,如果考试成绩不在0-100范围内时,则撤销插入或修改操作. 2.对SC表进行插入时,如果学生的选课总学分超过30,则报错并撤销插入. 3.对SC表进行修改时,不允许修改考试成绩不及格的学生选课记录. 4.对STUDENT表进行插入时,如果年龄为空,则将该生年龄设置为所有学生年龄的平均值. 5.在STUDENT表中增加两列,分别用来存放学生的平均成绩和等级,并对SC表进行插入后,自动维护学生的平均成绩和等级(85-100,等级为"优"; 60-85,等

  • MySQL触发器的使用

    触发器可以在执行语句前或执行后触发其他 SQL 代码运行.触发器可以读取触发语句改变了哪些数据,但是没有返回值.因此可以使用触发器加强业务逻辑的约束而不需要在应用程序写对应的代码. 从上述描述可以看到,触发器可以简化应用程序的逻辑并且可以提升性能,这是因为使用触发器减少了应用程序和服务端的交互次数.同时,触发器有助于完成自动更新归一化和统计数据.例如,我们可以使用触发器自动统计交易订单总金额,订单数及平均客单价. 然而,MySQL 的触发器的应用场合也十分有限,如果你使用过其他数据库产品的触发器

  • MySQL触发器trigger的使用

    目录 一.触发器简介 1.什么是触发器? 2.触发器的特性 二..创建触发器 1.创建只有一个执行语句的触发器 2.创建有多个执行语句的触发器 3.NEW与OLD详解 三.查看触发器 1.SHOW TRIGGERS语句查看触发器信息 2.在information_schema.triggers表中查看触发器信息 四.删除触发器 一.触发器简介 1.什么是触发器? 触发器是与表有关的数据库对象,在满足定义条件时触发,并执行触发器中定义的语句集合. 2.触发器的特性 有begin end体,begi

  • 详解mysql触发器trigger实例

    目录 什么是触发器 创建触发器 现在有表如下: 这里再啰嗦几句: 限制和注意事项 总结 数据库触发器有以下的作用: MySQL好像从5.0.2版本就开始支持触发器的功能了,本次博客就来介绍一下触发器,首先还是谈下概念性的东西吧: 什么是触发器 触发器是与表有关的数据库对象,在满足定义条件时触发,并执行触发器中定义的语句集合.触发器的这种特性可以协助应用在数据库端确保数据的完整性. 举个例子,比如你现在有两个表[用户表]和[日志表],当一个用户被创建的时候,就需要在日志表中插入创建的log日志,如

  • MySQL数据库 触发器 trigger

    目录 一.基本概念 1.作用 2.触发器的优缺点 2.1.优点 2.2.缺点 二.创建触发器 1.基本语法 2.触发对象 3.触发时机 4.触发事件 5.注意事项 需求: 三.查看触发器 四.触发触发器 五.删除触发器 六.触发器的应用 6.完善 2.优化 一.基本概念 触发器是一种特殊类型的存储过程,触发器通过事件进行触发而被执行 触发器 trigger 和js事件类似 1.作用 写入数据表前,强制检验或转换数据(保证数据安全) 触发器发生错误时,异动的结果会被撤销(事务安全) 部分数据库管理

  • MySQL触发器运用于迁移和同步数据的实例教程

    1.迁移数据 进行数据库移植,SQL Server=>MySQL.SQL Server上有如下的Trigger SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO ALTER TRIGGER [trg_risks] ON dbo.projectrisk FOR INSERT, UPDATE AS BEGIN UPDATE projectrisk SET classification = case when calc>= 9 then 3 when

  • MySQL 触发器详解及简单实例

    MySQL 触发器简单实例 语法 CREATE TRIGGER <触发器名称>  --触发器必须有名字,最多64个字符,可能后面会附有分隔符.它和MySQL中其他对象的命名方式基本相象. { BEFORE | AFTER }  --触发器有执行的时间设置:可以设置为事件发生前或后. { INSERT | UPDATE | DELETE }  --同样也能设定触发的事件:它们可以在执行insert.update或delete的过程中触发. ON <表名称>  --触发器是属于某一个表

  • mysql 触发器用法实例详解

     MySQL触发器语法详解: 触发器 trigger是一种特殊的存储过程,他在插入(inset).删除(delete)或修改(update)特定表中的数据时触发执行,它比数据本身标准的功能更精细和更复杂的数据控制能力.触发器不是由程序调用,而是由某个事件来触发的.在有数据修改时自动强制执行其业务规则,经常用于加强数据的完整性约束和业务规则等.触发器可以查询其他表,而且包含复制的sql语句.触发器也可用于强制引用完整性.触发器可以强制比用check约束定义的约束更为复杂的约束. (一).CREAT

  • MySQL触发器概念、原理与用法详解

    本文实例讲述了MySQL触发器概念.原理与用法.分享给大家供大家参考,具体如下: 1.触发器的概念 触发器(trigger)是MySQL提供给程序员和数据分析员来保证数据完整性的一种方法,它是与表事件相关的特殊的存储过程,它的执行不是由程序调用,也不是手工启动,而是由事件来触发,比如当对一个表进行操作(insert,delete, update)时就会激活它执行.--百度百科 上面是百度给的触发器的概念,我理解的触发器的概念,就是你执行一条sql语句,这条sql语句的执行会自动去触发执行其他的s

  • MySQL 触发器定义与用法简单实例

    本文实例讲述了MySQL 触发器定义与用法.分享给大家供大家参考,具体如下: 语法 CREATE TRIGGER 触发器名称  --触发器必须有名字,最多64个字符,可能后面会附有分隔符.它和MySQL中其他对象的命名方式基本相象. { BEFORE | AFTER }  --触发器有执行的时间设置:可以设置为事件发生前或后. { INSERT | UPDATE | DELETE }  --同样也能设定触发的事件:它们可以在执行insert.update或delete的过程中触发. ON 表名称

随机推荐